looping over character values in SAS

3

votes

In Stata you can loop over a list of character values with the foreach command. I've been trying to do the same in SAS to no avail so far. I'm trying to run a series of data and proc statements over all the values of character column. I tried with the following:

%let mylist = a b c; * These are all the values of a column called "code";

data mydata_@mylist; * Each element creates a new table;       
   set mydata;   
   where code=&mylist; 
   run;

What am I doing wrong or missing?

Thanks in advance,

Matías

loopsmacroscharactersas

6

votes

Try this:

%macro loopit(mylist);
   %let n = %sysfunc(countw(&mylist));
   %do I=1 %to &n;
      %let val = %scan(&mylist,&I);

      data mydata_&val;
         set mydata;
         where code = "&val";
      run;
   %end;
%mend;

%let list=a b c;
%loopit(&list);

2

votes

Modified version based on what DomPazz provided:

data mydata;
length code $8;
input code;
cards;
a
a
b
c
n
;
run;

%macro loopit(mylist);
    %let else=;
   %let n = %sysfunc(countw(&mylist));
    data 
   %do I=1 %to &n;
      %let val = %scan(&mylist,&I);
      mydata_&val
    %end;
        other
        ;


         set mydata;
   %do j=1 %to &n;
      %let val = %scan(&mylist,&j);
        %if &j ne 1 %then %do;
        %let else=else;
        %end;
      &else if code = "&val" then output mydata_&val;
        %if &j = &n %then %do;
        else output other;
        %end;
   %end;
   run;
%mend;

options mprint;
%let list=a b c;
%loopit(&list);

Here I process input data only once (for efficiency if you need) creating all output tables in one data step. Also I'm creating an "Other" table. To process only records with code from list is desired, you can add WHERE statement under SET statement and omit else output other;

0

votes

%macro getName;
%let name = %sysfunc(translate(&val, __, -/));
%mend;

%macro loopit(mylist);
    %let else=;
     %let name=;
   %let n = %sysfunc(countw(&mylist, %str( )));
    data 
   %do I=1 %to &n;
      %let val = %scan(&mylist,&I, %str( ));
        %getName
      mydata_&name
    %end;
        other
        ;


         set mydata;
   %do j=1 %to &n;
      %let val = %scan(&mylist,&j, %str( ));
        %getName
        %if &j ne 1 %then %do;
        %let else=else;
        %end;
      &else if code = "&val" then output mydata_&name;
        %if &j = &n %then %do;
        else output other;
        %end;
   %end;
   run;
%mend;

options mprint;
%let list=a-a b/b c-;
%loopit(&list);

This version uses COUNTW and SCAN functions with modifiers to only use space (%str( )) as a word separator.

Also uses new macro getName to compose a name for datasets according to SAS naming rules (note %let name = ; to simply provide variable inside %loopit for %getName to fill).

%getName translates not allowed characters to underscore (a potential name conflict here if you have same values with different separator).

looping over character values in SAS

3 Answers