SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

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

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! 


Accepted Solutions
Solution
‎09-13-2016 12:14 AM
Super User
Posts: 10,028

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


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

View solution in original post


All Replies
Solution
‎09-13-2016 12:14 AM
Super User
Posts: 10,028

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


%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;
Super Contributor
Posts: 441

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

Thank Ksharp!

 

 

Valued Guide
Posts: 505

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


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
Valued Guide
Posts: 505

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


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;
Valued Guide
Posts: 505

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

Posted in reply to rogerjdeangelis
It might be worth making each child dataset a SAS view. Especially if you plan to doing something immediately with the children.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 494 views
  • 2 likes
  • 3 in conversation