Creating tables from a master table based on a condition in SAS

Question

The data looks like follow:

ID Var1 Var2

A 1 2

A 3 4

B 5 6

B 7 8

C 9 10

D 11 12

I am trying to create subset tables from the above master table in SAS such that each subset table has all the records from the master table where ID equals each distinct value. Hence, in this particular example, I would like to dynamically create 4 different tables Tab_A, Tab_B, Tab_C, Tab_D where Tab_A contains all the records from master table where ID=A and so on. Total number of tables to be created=distinct values of ID.

Answer 1

This is a good case for creating a macro. SAS Macros write SAS code dynamincally.

This macro reads the distinct ID values, and then generates the data step to do the subset. It uses the mprint option so you can look at the code it creates.

data have;
input ID $ Var1 Var2;
datalines;
A 1 2
A 3 4
B 5 6
B 7 8
C 9 10
D 11 12
;
run;

%macro subset();
proc sql noprint;
select count(distinct id)
    into :nIDs TRIMMED
    from have;

select distinct ID
    into :ID1 - :ID&nIDs
    from have;
quit;

data 
%do i=1 %to &nIDs;
    tab_&&id&i
%end;
;
set have;

select (ID);
    %do i=1 %to &nIDs;
        when ("&&id&i") output tab_&&id&i;
    %end;
    otherwise put "PROBLEM";
end;
run;
%mend;

options mprint;
%subset;

Here is the log that shows the SAS Data step that actually does the subset.

MPRINT(SUBSET):   data tab_A tab_B tab_C tab_D ;
MPRINT(SUBSET):   set have;
MPRINT(SUBSET):   select (ID);
MPRINT(SUBSET):   when ("A") output tab_A;
MPRINT(SUBSET):   when ("B") output tab_B;
MPRINT(SUBSET):   when ("C") output tab_C;
MPRINT(SUBSET):   when ("D") output tab_D;
MPRINT(SUBSET):   otherwise put "PROBLEM";
MPRINT(SUBSET):   end;
MPRINT(SUBSET):   run;