BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sr3243
Calcite | Level 5

I want to combine rows with the same ID from a "long" data set into a "wide" data set based on common ID numbers.  File is currently in excel format.  Some IDs may have only 1 row, but others have up to 30 rows.  There are 5 variables per row (including the ID number).

Thanks!

 

Data have:

IDNumDateResultType
111/1/2013words1
2101/10/2013words3
226/1/2014words4
257/1/2015words2
3233/1/2014words1
346/2/2015words2
424/1/2013words1

 

Data want (with var going up to Num30, Date30, Result30, Type30)

IDNum1Date1Result1Type1Num2Date2Result2Type2Num3Date3Result3Type3
111/1/2013words1        
2101/10/2013words326/1/2014words457/1/2015words2
3233/1/2014words146/2/2015words2    
424/1/2013words1        

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

The simplest way is using proc means + idgroup .

Or if you have big table check MERGE skill proposed by me , Matt, Arthur.T 

 

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

 

 

 

data have;
infile cards expandtabs truncover;
input ID	Num	Date : $20.	Result $	Type;
cards;
1	1	1/1/2013	words	1
2	10	1/10/2013	words	3
2	2	6/1/2014	words	4
2	5	7/1/2015	words	2
3	23	3/1/2014	words	1
3	4	6/2/2015	words	2
4	2	4/1/2013	words	1
;
run;


proc sql noprint;
 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(drop=_:) idgroup(out[&n] (Num Date Result Type)=);
run;

 

View solution in original post

6 REPLIES 6
LinusH
Tourmaline | Level 20
Usually this is a format for a report, not a dataset.
But if you actually bed this take a look at PROC TRANSPOSE.
Data never sleeps
Ksharp
Super User

The simplest way is using proc means + idgroup .

Or if you have big table check MERGE skill proposed by me , Matt, Arthur.T 

 

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

 

 

 

data have;
infile cards expandtabs truncover;
input ID	Num	Date : $20.	Result $	Type;
cards;
1	1	1/1/2013	words	1
2	10	1/10/2013	words	3
2	2	6/1/2014	words	4
2	5	7/1/2015	words	2
3	23	3/1/2014	words	1
3	4	6/2/2015	words	2
4	2	4/1/2013	words	1
;
run;


proc sql noprint;
 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(drop=_:) idgroup(out[&n] (Num Date Result Type)=);
run;

 

sr3243
Calcite | Level 5

This was exactly what I needed!  Thanks!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, if your designing an e.g. a pdf or rtf file, then I would suggest just doing this in the proc report step:

http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

Shows several examples.

 

I would not recommend doing this if your just using this as data as it will make your programming far more complicated than it needs to be, you have the ideal structure already - fixed, if you change it to your suggestion then you need to know how many rows appear, program in to do array looping etc.

sr3243
Calcite | Level 5

I have ~2000 unique IDs and a few thousand rows, so I do need a program to avoid mistakes with manually cutting/pasting observations.

 

Thanks

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, what do you mean cutting and pasting?  That sounds like Excel, if so then you are producing a report - look at the link I gave with regards to doing this in the proc report which generates the Excel file.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 14938 views
  • 1 like
  • 4 in conversation