BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

%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

5 REPLIES 5
Ksharp
Super User

%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;
rogerjdeangelis
Barite | Level 11

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
Barite | Level 11

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
Barite | Level 11
It might be worth making each child dataset a SAS view. Especially if you plan to doing something immediately with the children.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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