- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Code: Program
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Code: Program
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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