SASHELP / DICTIONARY

VCATALG / CATALOGS
SAS catalogs

VCOLUMN / COLUMNS
Data set columns and attributes

VEXTFL / EXTFILES
Allocated filerefs and external physical paths

VINDEX / INDEXES
Data set indexes

VMACRO / MACROS
Global and automatic macro variables

VMEMBER / MEMBERS
SAS data sets and other member types

VOPTION / OPTIONS
Current SAS System option settings

VTABLE / TABLES
SAS data sets and views

VTITLE / TITLES
Title and footnote definitions

VVIEW / VIEWS
SAS data views

CAT/CATS/CATT/CATX Functions

These Functions and Call Routines can be used to join two or more strings together.
Even though we can use the concatenation operator in combination with the STRIP, TRIM, or LEFT functions, these functions make it much easier to put strings together and if you wish, to place one or more separator characters between the strings.
One advantage of using the call routines than the functions is improved performance.
Note: *Call routine executes faster than the function… in specific…

CALL CATS:
To concatenate two or more strings, removing both leading and trailing blanks.
CATS () stands for concatenate and strip.
Just think the ‘S’ at the end of the CATS as “Strip Blanks”.
Syntax: CALL CATS (result, string-1<, string-n>);

Example:

A=”ABC”;
B=” CONSULTING”;
C=”INC “;
D=” TEAM “;

FUNCTION= RESULT
CALL CATS(RESULT, A,B)= "ABCCONSULTING"
CALL CATS(RESULT, A,B,C)= "ABCCONSULTINGINC"
CALL CATS(RESULT, "HELLO",D)= "HELLOTEAM"

CALL CATT:
To concatenate two or more strings, removing only trailing blanks.
Just think the ‘T’ at the end of the CATT as “Trailing Blanks” or “Trim Blanks”.
Syntax: CALL CATS (result, string-1<, string-n>);

Example:

A=”ABC”;
B=” CONSULTING”;
C=”INC “;
D=” TEAM “;

FUNCTION= RESULT
CALL CATT(RESULT, A,B) ="ABC CONSULTING"
CALL CATT(RESULT, A,B,C)= "ABC CONSULTINGINC"
CALL CATT(RESULT, "HELLO",D)= "HELLO TEAM"

CALL CATX:
To concatenate two or more strings and removing both leading and trailing blanks and places a single space, or one or more characters of our choice, between each strings.
Just think the ‘X’ at the end of the CATX as “add eXtra blank.”
Syntax: CALL CATX (separator, result, string-1<, string-n>);

Example:

A=”ABC”;
B=” CONSULTING”;
C=”INC “;
D=” TEAM “;

FUNCTION =RESULT
CALL CATX(" ",RESULT, A,B) ="ABC CONSULTING"
CALL CATX(" ,", RESULT, A,B,C) ="ABC,CONSULTING,INC"
CALL CATX(' / ', RESULT, "HELLO",D) ="HELLO/TEAM"
CALL CATX(' *** ', RESULT, "HELLO",D) = "HELLO***TEAM"

FMTLIB

Adding the keyword FMTLIB to the PROC FORMAT statement displays a list of all the formats in your catalog, along with descriptions of their values.

libname library 'c:\sas\formats\lib';

proc format library=library fmtlib;
run;

Programming Methodology (Stanford)

Combining a Grand Total with the Original Data

*** Output grand total of sales into a data set;
Proc means data=videos;
      var sales;
      output out=summarydat sum(sales)=grandtotal;

*** Combine the grand total with the original data;
data videosummary;
      IF _N_=1 THEN SET summarydat;
      SET videos;
      percent=sales/grandtotal * 100;


Output:

sales grandtotal percent
1930 12880 14.9845
2250 12880 17.4689
...

System Options for Debugging Macro Errors

MERROR (default) | NOMERROR

When this option is on, SAS will issue a warning if you invoke a macro that SAS cannot find.

SERROR (default) | NOSERROR

When this option is on, SAS will issue a warning if you use a macro variable that SAS cannot find.

MLOGIC | NOMLOGIC (default)

When this option is on, SAS prints in your log details about the execution of macros.

MPRINT | NOMPRINT (default)

When this options is on, SAS prints in your log the standard SAS code generated by macros.

SYMBOLGEN | NOSYMBOLGEN (default)

When this options is on, SAS prints in your log the values of macro values.

Customize page numbers in RTF output

ods escapechar='^';
ods listing close;
ods rtf file='c:\tests\test.rtf';

data test;
      do i=1 to 50;
          output;
      end;
run;

proc print data=test noobs;
      title 'Page ^{thispage} of ^{lastpage}';
      footnote '^{pageof}';
run;

ods listing;
ods rtf close;

Macro Functions: %EVAL and %SYSEVALF

%EVAL function only supports integer arithmetic values. Macro statements performing integer arithmetic calculations:

%let one=%eval (3+5);
%let two=%eval (5*2);
%let three=%eval (9/3);
%let four=%eval (5/2);
%put The value of one is &one;
%put The value of two is &two;
%put The value of three is &three;
%put The value of four is & four;

Open the Log file and see the results as follows:
The value of one is 8
The value of two is 10
The value of three is 3
The value of four is 2

The value for macro variable four, should be 2.5, instead it shows only two. That happens because if we perform division on integers, integer arithmetic doesn’t take the fractional part into account.

%let last= %eval (5.0+3.0); /*INCORRECT*/

The values here in the above statement have a period character to numeric values and because of that the macro processor stops evaluating and produces the following error message: “ ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 5.0+3.0 “


Evaluating Floating Point Operands

The %SYSEVALF function can perform arithmetic calculations with operands that have the floating point values.

%let test= %sysevalf(1.0*3.0);
%let final= %sysevalf(1.5+2.8);
%let last= %sysevalf(5/3);
%put The value of test is &test;
%put The value of final is &final;
%put The value of last is &last;

The %PUT statements display the following messages in the log:
The value of test is 3
The value of final is 4.3
The value of last is 1.66666666666666

%SYSEVALF function perform arithmetic calculations and the result of the evaluation can be a floating point value like in the final and last macro variable case, but as in integer arithmetic calculations, the result is always a text.

The %SYSEVALF function be used in conjugation with other functions like, INTEGER, CEIL, and FLOOR.

For example, the following %PUT statements return 3, 4 and 3 respectively:
%let val=3.8;
%put %sysevalf(&val,integer); *Value returns in the log is 3;
%put %sysevalf(&val,ceil); *Value returns in the log is 4;
%put %sysevalf(&val,floor); *Value returns in the log is 3;


Difference between %eval and %sysevalf functions can be understand better with the following example;

%let value=9;
%let value2=5;
%let newval=%sysevalf(&value/&value2);
%let newval1=%eval(&value/&value2);
%put &newval;
%put &newval1;

*Ans: newval=1.8;
*Ans: newval1=1;

Functions: YRDIF, DATDIF, INTCK

Using YRDIF function:
“act/act” will gives us the actual interval between the two dates.
To know the interval between two dates in Years:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      years=yrdif(sdate,edate,'act/act');
      put years;
run;

Output: 10.2535 yrs


Using DATDIF function:
To know the interval between two dates in Days:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      days=datdif(sdate,edate,'act/act');
      put days;
run;

output: 3745 days


Using the INTCK function:
The INTCK function returns the integer count of the number of intervals in years, months or days between two dates.

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      years=intck(‘year’,sdate,edate);
      put years;
run;

output:10 years


To know the interval between 2 dates in days:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      days=intck(‘days’,sdate,edate);
      put days;
run;

result: 3745 days


To know the interval between 2 dates in months:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      months=intck(‘months,sdate,edate);
      put months;
run;

result: 123 months

Phonetic Matching (=*) & Pattern Matching (% and _)

Phonetic matching (Sounds-Like Operator =*)
e.g.
where custname =* 'Lafler';

Finding patterns in a string (Pattern matching % and _)
e.g.
where prodtype like '%soft%';
where prodtype like '____a%';

LIKE Clause in CREATE TABLE

The LIKE clause tiggers the existing table's structure to be copied to the new table minus any column dropped with the KEEP= or DROP= option.

proc sql;
      create table hot_products
      like products;
quit;

LOG Results:
NOTE: Table HOT_PRODUCTS created, with 0 rows and 5 columns.

Call Execute

Data step call routine
call execute('%sales');
call execute('%sales('||month||')');
The following DATA step uses CALL EXECUTE to execute a macro only if the DATA step writes at least one observation to the temporary data set.

%macro overdue;
proc print data=late;
title "Overdue Accounts As of &sysdate";
run;
%mend overdue;

data late;
set sasuser.billed end=final;
if datedue<=today()-30 then
do;
n+1;
output;
end;
if final and n then call execute('%overdue');
run;

SYSPARM

Conditionally Execute Batch Jobs

data one;
      set one;
      if scan(sysparm(),1) ^= ' ' then do;
......

data _null_;
      array p{3} p1-p3;
      inp=sysparm();
      if inp='' then abort abend;
      do i = 1 to 3;
        p[i]=scan(inp,i);
      end;
      call symput('v1',left(trim(p1)));
......


sas program.sas -sysparm "XYZ ABC OPQ"

MOD function

MOD (dividend-expression, divisor-expression)

Returns the remainder from the division of dividend-expression by divisor-expression.

Statements	Results
a=mod(10,3);	1
a=mod(.35,-.1);	0.05

Sample program: (select certain observations from a dataset)

data temp;
       set temp;
       if mod(_N_,3)=0;
run;

Options VALIDVARNAME=ANY

VALIDVARNAME=V7 V6 UPCASE ANY

V7 - (default) indicates that up to 32 mixed case alphanumeric characters are allowed. Names must begin with alphabetic characters or an underscore.

V6 - only 8 bytes long.

UPCASE - variable names are uppercased.

ANY - allows any characters to appear as valid SAS variable names. Symbols, such as "=" and "*", must be contained in a 'varname'n construct.
e.g.
libname foo ......;

data foo.'My Table'n;

input 'Amount Budgeted'n 'Amount Spent'n 'Amount Difference'n;

Calculate Age

%macro age(date,birth);

floor((intck('month',&birth,&date)
    - (day(&date) < day(&birth))) / 12);

%mend age;

Compress

Strip off some typical special characters:

test2=compress(wordvb, '0D'x);
*** remove the carriage return;

test2=compress(test2, '0A'x);
*** remove the line feed;

test2=compress(test2, 'A0'x);
*** remove non-breaking space;

varname = compress(varname, , 'kw');
*** The modifier “k” stands for ‘KEEP’ and the modifier “w” stands for ‘WRITABLE’. Note that there is no second parameter in the above code. When compress function is used in combination of K & W modifiers, it keeps all the writable characters which means it deletes all the non writable characters.

Case Logic Versus COALESCE Expression

PROC SQL;
      SELECT CUSTNAME,
        CASE
          WHEN CUSTCITY IS NOT NULL THEN CUSTCITY
          ELSE 'Unknown'
        END AS Customer_City
      FROM CUSTOMERS;
QUIT;

PROC SQL;
      SELECT CUSTNAME,
        COALESCE(CUSTCITY, 'Unknown')
        AS Customer_City
      FROM CUSTOMERS;
QUIT;

ANSI Standard

The ANSI standard reserves a number of SQL keywords from being used as column names. If a column name conflicts with a reserved word,

PROC SQL DQUOTE=ANSI;
      SELECT *
      FROM RESERVED_WORDS
      WHERE "WHERE"='EXAMPLE";
QUIT;

Using Macros to Skip a Section of Code

Just put a “%macro name” at the top and a “%mend” at the end and never call the macro. This effectively comments out the code without running it. No need to worry about other comment styles in the code.

I. Statement Can Be Repeated Or Nested

SAS statements you want to execute.....
%macro SKIP ;
      SAS statements you want to skip.....
%mend SKIP ;

SAS statements you want to execute.....

%macro SKIP ;
      More SAS statements you want to skip.....
%mend SKIP ;
SAS statements you want to execute.....

II. Use NOSOURCE Option

Options Nosource;
%macro SKIP ;
      SAS statements you want to skip.....
%mend SKIP ;

Options Source;
SAS statements you want to execute.....