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!
%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;
%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;
Thank Ksharp!
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
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;
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.
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.