Creating Data Driven Programs with the Macro Language

Creating dynamic, data driven programs is a very powerful tool. Often we can use the metadata or the project data itself to help write our programs. This paper will introduce some of the concepts related to writing SAS programs which will generate pieces of SAS code in a data driven manner.

Parts of the SAS language used in this discussion include the following:

1. macro call routines (call execute, call symput)

2. PROC SQL

3. SAS Dictionary Tables

A major problem that can occur in project code is hard coding project specific facts into a program. A good example is a program used across multiple protocols in a clinical trial. For instance, we may know that all protocols except for 3 and 5 already have the variable age

calculated in the demographics module. This may tempt us to have something like the following in our program.

%if &prot.=3 or &prot=5 %then

%do;

%* calculate the age variable.;

%end;

This works fine but what happens if more protocols come up at a later time which also lack the age variable. The code will have to be changed when this happens. It may be beneficial to programmatically check to see if the variable age exists in the demographics module. Based on that data driven check, the age variable can be calculated if necessary.

Using the parts of the SAS language and programming techniques discussed in this paper can result in low maintenance, reusable programs. They can also sometimes save a lot of typing!

This paper is intended for those with previous exposure to macro and SQL. Please be advised that some of these SAS tools are not available prior to SAS version 6.12.

PARTS OF THE SAS LANGUAGE

First we will review the parts of the SAS language utilized in this paper.

1. CALL SYMPUT

Call symput is a data step statement used to create a macro variable from a value or data set variable. Following is an example…

data _null_;

 call symput(‘macvar’,’macro variable value’);

run;

The first argument is the name of the macro variable. The second argument is the macro variable value. This can also be a data set variable.

CALL EXECUTE

Call execute is also a data step statement which is used to execute whatever is passed into it. An example follows…

data _null_;

 call execute(‘proc options; run;’);

run;

There is only one argument in a call execute.

Caution: There is a version 7 bug which compresses out spaces when combining multiple call executes. For example, the following code runs in version 6.12 but not in version 7.

data _null_;

 call execute(‘proc ’);

 call execute(‘options; run;’);

run;

Version 7 ends up trying to execute the code

procoptions; run; instead of proc options; run;

2. PROC SQL

PROC SQL will be used in this paper to access the dictionary tables as well as create macro variables similar to how we can use call symput.

An example follows which places the data set label from SASHELP.RETAIL into a macro variable called dslabel;

proc sql noprint;

 select memlabel into :dslabel

 from dictionary.tables

 where libname=’SASHELP’ and memname=’RETAIL’;

quit;

Following is an example creating sequential macro variables from multiple records. This loads variable names from SASHELP.RETAIL into macro variables var1, var2, …, varn. Even though room is left for 9,999 sequential macro variables only the necessary ones are created.

proc sql noprint;

 select name into :var1 - :var9999

 from dictionary.columns

 where libname=’SASHELP’ and memname=’RETAIL’;

quit;

Following is an example creating one concatenated macro variable from multiple records. This loads variable names from SASHELP.RETAIL into macro variable varlist separated by whatever we specify. This provides us with a handy list of variables to include in a keep= or drop= data

set option later in the program.

proc sql noprint;

 select name into :varlist separated by ‘ ‘

 from dictionary.columns

 where libname=’SASHELP’ and memname=’RETAIL’;

quit;

If you are interested in building up a macro variable of quoted values try the following separated by ‘ ”,” ’