Using double semicolon with macro statement in data step

Using macro statement within data step.

Each macro statement ends with a semicolon. If the macro statement involved generating new data step statement, it is important to distinguish the semicolon that is part of the data step statement and the semicolon that is part of the macro statement. When data step statement is nested inside a macro statement, we need to use double semicolons to end the whole macro part. e.g.

data a;

set a;

%if &group= %then _group=1;

%else rename &group=_group;;

run;

The macro in the data step will resolve to either ‘_group=1’ or ‘rename &group=_group’, depending on the value of &group. Note the resolved statement does not have semicolon by itself. Now, note the double semicolon at the end of the %if %else macro. The first semicolon (red) represents the end of the %if %else macro; then second semicolon (yellow) represents the semicolon that is carried over for the resolved data step statement (either _group=1 or rename &group=_group). Now the resolved data step statement is ended with a semicolon.

Visualizing PROC TRANSPOSE

Original paper: http://www.nesug.org/proceedings/nesug07/cc/cc03.pdf

The BY statement: indicating the variable that should stay the same. Variables that need to be kept in their original structure should be specified in the BY statement.

The ID statement: indicating the variable that should go up (from row to column). This means that the individual values of VERTVAR are to be turned into variables themselves. One variable for each unique value of VERTVAR will be created.

The VAR statement: indicating the variable that should go down (from column to row). This means that the distinct variables by VAR will now be distinct values of the newly created variable _NAME_. And the corresponding values of the VAR variables will be the new values of the ID variables. We can use the NAME= option to rename the variable _NAME_.

Image above is from SAS transpose procedure online documentation

CAT rountines and functions

CALL CATS, CALL CATT, and CALL CATX are three call routines that concatenates strings. CAT, CATS, CATT and CATX are equivalent functions.

Call routines are said to be more efficient than the corresponding functions.

The syntax is just a little different between call routines and functions. The call routine puts the resulting string as the 1st argument while the function puts the resulting string on the left side of "=" sign, e.g.:

CALL CATS(newstring, string1, string2)

newstring=CATS(string1, string2)

|| is another operator that concatenates the strings. But it does not automatically takes care of the leading and trailing blanks. So usually it needs to be used in conjunction with STRIP() function, e.g., strip(string1)||strip(string2). Such STRIP() function becomes an annoyance if multiple strings are being concatenated. Therefore, the CAT functions are preferred over || if the string is referred by variables. || is better to be used with literal string since there is no leading or trailing blanks.

CALL CATS or CATS: Concatenate strings and strips leading and trailing blanks.

CALL CATT or CATT: Concatenate strings and trims only the trailing blanks.

CALL CATX or CATX: Concatenate strings, strings leading and trailing blanks and separate each string with a separator string, i.e. adding extra separator between strings. The default separator is blank. e.g. CALL CATX(",", newstring, string1, string2) - concatenate string1 and string2 and separate them by ","

Appending library into FMTSEARCH= option

FMTSEARCH= provides a convenient way for SAS to search for user defined formats at run time. The syntax is simple: just put the library names inside the parenthesis.

options FMTSEARCH=(yourlib)

Sometimes, user needs to append another directory to current search list. However, calling "options FMTSEARCH=" again will not make it happen - this will overwrite the old directory list with the new directory list.

If you are using SAS 9.1, what you can do is to get the current library list by using the GETOPTION function, storing the list in a macro variable and then append the new library list at the end of the macro variable. One trivial thing to note is that the GETOPTION returns the list with a pair of parenthesis. One way to get

rid of them is to use the COMPRESS function. The code follows:

%let _OLDLIB=%sysfunc(compress(%sysfunc(getoption(fmtsearch)), ")"));

options FMTSEARCH=&_OLDLIB, newlib);

In SAS 9.2, APPEND= option is introduced to the system options. For those lucky ones, simply use the following statement:

options APPEND=(FMTSEARCH=(newlib));

I am still waiting for my 9.2 to be installed. So, saving this new feature for later.

SAS macro string functions

SAS offers a rather short list of macro functions for string manipulation. Unlike regular SAS string functions (the ones used in a data set, without '%' in the front), these functions can be used directly in macro programming.

• %INDEX(source, string): Returns the position of the first character of a string.
• %SCAN(source, n, string, modifier): Search for the nth word in a string. Use modifier to add search options.
• %QSCAN: same as %SCAN but does not resolve special characters such as % and &.
• %SUBSTR(source, n, length): produce a substring of argument, beginning at position, for length number of characters.
• %QSUBSTR: same as %SUBSTR but does not resolve special characters such as % and &.
• %BQUOTE: Mark special characters ' " ( ) + - * / < > = ¬ ^ ~ ; , # blank AND OR NOT EQ NE LE LT GE GT IN
• %NRBQUOTE: same as %BQUOTE but in addition mark: & %.

The complete list of SAS macro functions can be found at: http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a003167026.htm

With such short list of function, you may not always find the functions you want to use. What if you want to used a regular data step string function such as lowcase() or anydigit()? Well, you can always use %sysfunc() function to 'borrow' the data step functions into the macros.
The complete list of SAS string functions can be found at (PDF file):
http://support.sas.com/publishing/pubcat/chaps/59343.pdf