Pass a dynamic array to iterate over proc sql

Question

In this question

Simple iteration through array with proc sql in SAS

%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);

I want to pass a list through macro doit which we can extract from a dataset. For eg.: list contains the distinct values of variable 'age' present in dataset 'agegroups'.

data agegroups;
input age;
datalines;
1
2
4
5
8
18
16
19
23;

I looked upon %macro array for it but it didnt help me out(http://www2.sas.com/proceedings/sugi31/040-31.pdf)

Any help will be highly appreciated. Thanks !

It sounds as though you are attempting to create lots of small datasets from a single large dataset. In SAS it is generally much simpler to create a single large dataset and utilise by-group processing to loop over distinct values. — user667489

DomPazz DomPazz · Accepted Answer · 2016-12-12T20:04:27

As stated in the comments, BY group processing might be a better option.

However, you can use PROC SQL to create your list:

proc sql noprint;
select distinct age
   into :ageList separated by ' '
   from agegroups;
quit;

%put Age List: &ageList;

%doit(&ageList);

Pass a dynamic array to iterate over proc sql

1 Answers