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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1219 views
  • 2 likes
  • 3 in conversation