Create macro for where condition in SAS

Question

I have a dataset which has more than 150+ names. Example - A1,B1,C1,…so on.

I have a code using which I'm getting sales for each name, right now I'm running the following query by selecting names manually.

create table want as 
select Name
,sum(sales) as sales
from have
where name in ('A1','B1')
group by Name
;quit;

I want to run this code in such a way that it picks one name from the list at a time and stores the results in a dataset then picks another name and stores in another dataset and so on. At the end appends all the 150+ datasets into one final dataset. Please help me do this. Thanks.

Answer 1

Let's assume your names are stored in a single dataset that looks as follows:

name
A1
B1
C1 
...

I'm going to assume that there could be duplicates of each one. We'll remove those duplicates, run a data step from call execute() to dynamically generate SQL for each name, then append each dataset to a single dataset at the end.

proc sort data=have(keep=name) out=names nodupkey;
    by name;
run;

data _null_;
    set names;

    call execute(cat(
     'proc sql;',
     '    create table tmp_', _N_, ' as',
     '        select name, sum(sales) as sales',
     '        from have',
     '        where name=', quote(name),
     '        group by name;',
     'quit;')
    );
run;

data want;
    set tmp_:;
run;