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
– Joe, Commented Sep 7, 2013 at 18:58

DomPazz · Accepted Answer · 2013-09-06 22:26:10Z

7

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

answered Sep 6, 2013 at 22:26

DomPazz

12.4k19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

America Over a year ago

This is just what I needed to get my head around the paradigm. Thanks a lot for this great example!

America Over a year ago

How could I alter this to accommodate a two-dimensional list/array? For example instead of (100, 101, 102) say I now have ([100,'02-May-2013'],[101,'14-Apr-2013'],[102,'22-Aug-2013']) and I want to iterate through both the item number and the dates to do some procedure conditional on both values. Thanks again!

DomPazz Over a year ago

iterating through items in a dataset is pretty easy. Hard to answer that in a comment. Ask a separate question and I'm sure it will get answered pretty quickly.

Collectives™ on Stack Overflow

Simple iteration through array with proc sql in SAS

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related