Simple iteration through array with proc sql in SAS

Question

I just want to loop through a list and run a procedure using the 'i'th element in the list, and make some table named 'i' with the result. I've tried the syntax from every resource I can find but can't get this to work. Here's some code/pseudocode that illustrates my need. Many thanks in advance!

array itemlist[*] (100,101,102);

proc sql;
    do i=1 to dim(itemlist);
    create table somelibname.[itemlist(i)] as
        select * from somelibname.sometable
        where item=itemlist(i);
    end;
quit;

It's likely you don't actually need to create separate tables for each element. Most procedures in SAS will take the BY statement, which allows you to act as if each value of the BY variable(s) are distinct tables. See for example the paper Don't be LOOPY . — Joe

DomPazz DomPazz · Accepted Answer · 2013-09-06T22:26:10

You need to use a macro to "write" the SAS code for you.

This should do what you are looking for. It takes a space delimited list of values, and loops over them doing what your code specifies. Post a comment if you have a question on it.

%macro doit(list);
proc sql noprint;
%let n=%sysfunc(countw(&list));
    %do i=1 %to &n;
        %let val = %scan(&list,&i);
        create table somlib._&val as
            select * from somlib.somtable
            where item=&val;
    %end;
quit;
%mend;

%doit(100 101 102);

Note, data sets cannot start with a number so I have these starting with '_'

Simple iteration through array with proc sql in SAS

1 Answers