Create single records from multiple rows

[inquisitive_man]

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

[Tom]

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

[inquisitive_man]

@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!

[Ksharp · Accepted solution]

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;

[inquisitive_man]

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!

[MikeZdeb]

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