splitting a table into many tables while retaining an id variable for each new table

[ilikesas]

Hi,

suppose that I have a data table consisting of 3 columns: id, name ,age.

id	name	age
1	john	25
2	suzy	30

What I would like to do is to split this table into smaller tables consisting of the id and a variable, so is this case I will have 2 new tables:

one table will consist of id and name, and the other will consist of the id and age:

id	name
1	john
2	suzy

id	age
1	25
2	30

For simplicity in this example I have only 3 variables, but it will be really helpful if I could have a code that will dynamically do this for any number of variables. Tried to think about how to do it but can't figure out.

Thank you! 

[Ksharp · Accepted solution]

%let dsn=sashelp.class;
%let id=name;

proc transpose data=&dsn(drop=&id) out=temp;
 var _all_;
run;
data _null_;
 set temp;
 call execute(cat("data ",_name_,";set &dsn;keep &id ",_name_,";run;"));
run;

[ilikesas]

Thank Ksharp!

[rogerjdeangelis]

This should be faster because it all runs in one address space, but who knows all the details og
the dosubl architechure.

data have;
input id name $ age;
cards4;
id name age
1 john 25
2 suzy 30
;;;;
run;quit;

data _null_;
   do name='name','age';
     call symputx('nam',name);
     rc=dosubl(resolve('
        data &nam;
           set have(keep=id &nam);
        run;quit;
     '));
   end;
run;quit;


146   data _null_;
147      do name='name','age';
148        call symputx('nam',name);
149        rc=dosubl(resolve('
150           data &nam;
151              set have(keep=id &nam);
152           run;quit;
153        '));
154      end;
155   run;

SYMBOLGEN:  Macro variable NAM resolves to name
SYMBOLGEN:  Macro variable NAM resolves to name
NOTE: There were 3 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.NAME has 3 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              1031.71k
      OS Memory           13792.00k
      Timestamp           09/13/2016 06:04:42 AM
      Step Count                        31  Switch Count  0


SYMBOLGEN:  Macro variable NAM resolves to age
SYMBOLGEN:  Macro variable NAM resolves to age
NOTE: There were 3 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.AGE has 3 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds                            Win 7 32bit SAS 9.3
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds                           Download and install 9.06 Ghostscipt
      memory              1031.71k
      OS Memory           13792.00k                              Scan the download
      Timestamp           09/13/2016 06:04:42 AM

[rogerjdeangelis]

This should be faster because it all runs in one address space, but who knows all the details of
the dosubl architechure.

data have;
input id name $ age;
cards4;
id name age
1 john 25
2 suzy 30
;;;;
run;quit;

data _null_;
   do name='name','age';
     call symputx('nam',name);
     rc=dosubl(resolve('
        data &nam;
           set have(keep=id &nam);
        run;quit;
     '));
   end;
run;quit;

[rogerjdeangelis]

It might be worth making each child dataset a SAS view. Especially if you plan to doing something immediately with the children.