Do loop within SAS Macro

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.

Solution

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.