DATA Step, Macro, Functions and more

Create single records from multiple rows

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Create single records from multiple rows

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
Solution
‎08-18-2015 08:32 AM
Super User
Posts: 10,046

Re: Create single records from multiple rows

Posted in reply to inquisitive_man

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,081

Re: Create single records from multiple rows

Posted in reply to inquisitive_man

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

New Contributor
Posts: 3

Re: Create single records from multiple rows

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

Solution
‎08-18-2015 08:32 AM
Super User
Posts: 10,046

Re: Create single records from multiple rows

Posted in reply to inquisitive_man

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;
New Contributor
Posts: 3

Re: Create single records from multiple rows

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!

Valued Guide
Posts: 765

Re: Create single records from multiple rows

Posted in reply to inquisitive_man

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1578 views
  • 13 likes
  • 4 in conversation