topic Re: Create single records from multiple rows in SAS Programming

Create single records from multiple rows

inquisitive_man — Mon, 17 Aug 2015 20:56:05 GMT

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

Re: Create single records from multiple rows

Tom — Tue, 18 Aug 2015 01:12:31 GMT

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

Re: Create single records from multiple rows

Ksharp — Tue, 18 Aug 2015 12:32:04 GMT

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;

Re: Create single records from multiple rows

inquisitive_man — Wed, 19 Aug 2015 17:53:00 GMT

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

Re: Create single records from multiple rows

inquisitive_man — Wed, 19 Aug 2015 17:54:39 GMT

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!

Re: Create single records from multiple rows

MikeZdeb — Wed, 19 Aug 2015 19:25:58 GMT

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