Executing SAS Code from string in dataset

Question

Is it possible to read (and execute) SAS code that is stored in a string in a SAS Dataset.

For example, the dataset 'CODE' contains a string variable, which contains

"IF TOTAL_SALES GE 20000 AND TYPE IN ('A', 'B', 'C') THEN VAR1 = 'Y' ;"

Can I do something like?

data sales ;
set sales ;
/* run the if statement above */
run ;

Using SAS 9.2

Answer 1

There are as many ways to do this as there are ways to construct macro variables. The three most useful typically:

proc sql select into

This allows you to create many lines of code instantly, and is probably the most common tool used for this purpose. It can either directly create lines of code, or more usefully create macro calls. For example, let's say you wanted to run:

data want;
set have;
x = sum(a,b,c);
run;

But x,a,b,c are all defined in another dataset. Further, you have 3 variables like this.

data callset;
input var1 $ var2 $ var3 $ var4 $;
datalines;
x a b c
y d e f
z b e c
;;;;
run;

You could construct it this way:

proc sql;
select cats(x,"=sum(",a,",",b,",",c,");") into :calllist
 separated by ' '
 from callset;
quit;

data want;
set have;
&calllist.
run;

However, it might be easier to construct a macro:

%macro sum(var1,var2,var3,var4);
&var1. = sum(&var2.,&var3.,&var4.);
%mend sum;

Then your PROC SQL is a bit easier (not really in this case, but often this helps readability in more complex code):

proc sql;
select cats('%sum(',catx(',',x,a,b,c),')') into :calllist
 separated by ' '
 from callset;
quit;

Then use it the same way.

The limitations here: You cannot modify the string while you construct it except for what PROC SQL allows you to do (which is powerful, but not datastep code which if you needed to use things like first.var you would have to do prior to the proc sql in a separate step). You have a limitation of around 20k characters in total in the macro variable.

The separated by is important, by the way; without it you can only create a single line of code (only the last one will be put into the macro variable). Even if you don't really want it separated by anything, you still need to separate by ' ' in order to generate the list.

%include file

The include file method is a hybrid of the proc sql method and call execute. It is constructed in a data step, and has no length limitations beyond your OS's file size limits. It is however a bit messier (in that a temporary file is created) and has the normal limitations of include files, such as not containing datalines.

You construct it this way (using the previous datasets):

filename toincl temp; *create temporary fileref;
data _null_;
set callset;
file toincl;
callstr = cats('%sum(',catx(',',x,a,b,c),')');
put callstr $;
run;

data want;
set have;
%include toincl;
run;

It gets around the PROC SQL length limitation, but has the normal limitations of an include file (see the documentation for more information).

call execute

This is used to execute a line of code interactively immediately following the data step. It is convenient as it allows you to construct code on the fly somewhat more flexibly than the other methods, but it has significant timing limitations.

data _null_;
set callset; *this is not the main data set, but the control file with SAS code;
call execute('data want; set have;');
callstr=cats('%sum(',catx(',',x,a,b,c),')');
call execute(callstr);
call execute('run;');
run;

The primary limitation that people usually have trouble with is macro variable timing. When a macro variable is defined in a CALL EXECUTE step, it is not available for use during the same CALL EXECUTE step. So if your code contains code to create and then use a macro variable, it will not behave correctly; you need to use one of the other methods. If you use this method, I highly recommend reading a few papers on CALL EXECUTE such as this one or this one first.

Answer 2

2 ways to do it:

1. Use call symput in a data step to put it into a macro variable. You then put the macro variable where you want the code to execute. (take into account the caveats of when SAS interprets and executes what)
2. Use call execute in a data step. Note that all the code entered into a call execute is accumulated during the data step and then interpreted and executed immediately after the data step.

For call execute, see: http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000543697.htm

Answer 3

You want to use the call symput function prior to create a macro variable containing the SAS code before you read in the sales data. Here is some example code:

* _null_ means there is no output dataset;
data _null_;
    set code;

    * assuming char variable containing the SAS code is called 'code_string';
    * if only certain rows contain the correct code_string values;
    * then you could use an if statement to assign &my_code as desired;
    call symput('my_code', code_string);
run;

data sales;
    set sales;

    * now access the value of the macro variable created with call symputx above;
    &my_code
run;

If the code dataset that has the char variable containing the SAS code string is only a single row (or can be restricted to a single row with a where statement), then you can use proc sql to achieve the same result as the _null_ data step above:

proc sql;
    select code_string into :my_code
    from code
    /* where [put your boolean expression here] */
;
quit;