Do loop within SAS Macro

Question

I am attempting to create a macro that will iterate through the string values of a column.

Here is the data:

SUBJECT          VISIT            PARAMETER
001              Baseline         param1
001              Visit 2          param1
001              Visit 3          param1
001              Baseline         param2
001              Visit 2          param2
001              Visit 3          param2
002              Baseline         param1
002              Visit 2          param1
002              Visit 3          param1
002              Baseline         param2
002              Visit 2          param2
002              Visit 3          param2

Here is the macro I have as of now:

%macro want(numb, string);

DATA want&numb;
   SET have;
   IF parameter = &string;
RUN;

%mend want;

In its current form I have to input both the number and the string value in the PARAMETER column. I would like to modify the macro so that I can iterate through 30 parameters rather than the following:

%want(1, 'param1');
%want(2, 'param2');
...
%want(30, 'param30');

Any insight would be greatly appreciated.

Are you asking how to pass multiple values of STRING to the macro? How long is each string? How many could you possible have? There is a 64K byte limit on the value of a single macro variable. — Tom
Yes that's correct. I'd like to iterate through the string values of the PARAMETER variable. I'm supposing there are 30 values, so the 'numb' value would go from 1 to 30. — statsguyz
What types of characters can appear in the values? Can the values contain spaces? Quotes? Or are they just single words? — Tom
Values can contain spaces as well as percentage symbols and parentheses. — statsguyz
First create a working program and then convert it into a macro. You can then use CALL EXECUTE() to execute the macro from data step directly for the parameters. See a tutorial here: github.com/statgeek/SAS-Tutorials/blob/master/… — Reeza

Tom Tom · Accepted Answer · 2020-04-25T17:37:59

If you want to pass multiple values to a macro call then it is usually easiest to use a delimited list. The macro can use the %SCAN() macro function to select individual values if it wants. You can use the COUNTW() function to determine the number of values. Wrap it in the %SYSFUNC() macro function to use it in macro logic.

Since you want to use the values as string literals in SAS code it would be easiest to just ask the users to include the quotes in the values they pass. The same as they would when writing the value in a SAS statement.

When you have all of the values at one time like this then you can use one data step to write all of the output datasets in one pass of the input data.

%macro split(inds,prefix,values);
%local i n dsname sep;
%let n=%sysfunc(countw(&values,%str( ),q));
data 
%do i=1 %do &n;
  %let dsname=&prefix.&n;
  &dsname.
%end;
;
  set &inds ;
%do i=1 %to &n;
  %let dsname=&prefix.&n;
  &sep. if parameter = %scan(&values,&i,q) then output &dsname.;
  %let sep=else;
%end;
run;
%mend ;

So then your example would be a call like this:

%split(inds=have,prefix=want,values='param1' 'param2' ... 'param30')

If you had a dataset with the list of values you can use a simple SQL query to build the list of values. Like this:

proc sql noprint;
  select distinct quote(trim(param),"'") into :parmlist separated by ' '
  from list_of_parameters
  ;
quit;

And use that macro variable in the call.

 %split(inds=have,prefix=want,values=&parmlist.)

If you want the number of datasets to generate to just be based on the values of PARAMETER that appear in HAVE then you can move that logic into the macro itself and make it easier on the users of the macro.

Note that in general there is no need to split a dataset like this. If you want to use the original dataset HAVE but only use the observations for a particular value of PARAMETER then just use a WHERE statement or WHERE= dataset option.

Do loop within SAS Macro

2 Answers