Say I have a SAS dataset created by the following:
data try;
input ID $ q1 $ q2 $;
datalines;
A R1 smart
A R3 queue
B R3 queue
C R1 smart
C R4 dumb
C R5 okay
;
run; quit;
Now I want to create a new dataset with the following structure (where I have each ID letter on a row and all its data):
A R1 smart R3 queue
B R3 queue
C R1 smart R4 dumb R5 okay
I am fairly new to SAS and was looking for a quick easy to understand way in how to do this. Anybody out there that can help
The simplest way is using proc summary.
Or you want check MERGE skill which has more fast speed and no need to reorder variable:
(me,Arthur.T, Matt)
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
input ID $ q1 $ q2 $;
datalines;
A R1 smart
A R3 queue
B R3 queue
C R1 smart
C R4 dumb
C R5 okay
;;;;
run;
proc sql;
select max(n) into : n
from (select count(*) as n from have group by id);
quit;
proc summary data=have nway;
class id;
output out=want idgroup(out[&n] (q1 q2)=);
run;
Normally you use PROC TRANSPOSE for this type of problem. You have complicated things by having multiple variables that you want to transpose together.
Since they are both character variables you can sort of trick it by using the first one as both a BY variable and an analysis variable in the first TRANSPOSE to make a tall skinny table. Then transpose again to make the desired output.
data have;
input ID $ q1 $ q2 $;
datalines;
A R1 smart
A R3 queue
B R3 queue
C R1 smart
C R4 dumb
C R5 okay
;;;;
proc transpose data=have out=tall ;
by id q1;
var q1 q2 ;
run;
proc transpose data=tall out=want prefix=newq;
by id ;
var col1 ;
run;
Obs | ID | _NAME_ | newq1 | newq2 | newq3 | newq4 | newq5 | newq6 |
1 | A | COL1 | R1 | smart | R3 | queue | ||
2 | B | COL1 | R3 | queue | ||||
3 | C | COL1 | R1 | smart | R4 | dumb | R5 | okay |
@Tom
Thank you for the prompt feedback. Your method works perfectly but in order to name the variables appropriately all within a singular step the method outlined by @xia keshan works best for my usage. Thanks again for the insight!
The simplest way is using proc summary.
Or you want check MERGE skill which has more fast speed and no need to reorder variable:
(me,Arthur.T, Matt)
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
input ID $ q1 $ q2 $;
datalines;
A R1 smart
A R3 queue
B R3 queue
C R1 smart
C R4 dumb
C R5 okay
;;;;
run;
proc sql;
select max(n) into : n
from (select count(*) as n from have group by id);
quit;
proc summary data=have nway;
class id;
output out=want idgroup(out[&n] (q1 q2)=);
run;
Thank you for your post and link to the article. It offered great insight on multiple topics. Ultimately I understand the article better than your proc summary above. Thank you again!
Hi. If you want an explanation of the PROC SUMMARY idea used by Xia, read this paper ...
Transposing Data Using PROC SUMMARY'S IDGROUP Option
http://support.sas.com/resources/papers/proceedings10/102-2010.pdf
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.