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;

Answer 1

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 '_'