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:
ID | Num | Date | Result | Type |
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 |
Data want (with var going up to Num30, Date30, Result30, Type30)
ID | Num1 | Date1 | Result1 | Type1 | Num2 | Date2 | Result2 | Type2 | Num3 | Date3 | Result3 | Type3 |
1 | 1 | 1/1/2013 | words | 1 | ||||||||
2 | 10 | 1/10/2013 | words | 3 | 2 | 6/1/2014 | words | 4 | 5 | 7/1/2015 | words | 2 |
3 | 23 | 3/1/2014 | words | 1 | 4 | 6/2/2015 | words | 2 | ||||
4 | 2 | 4/1/2013 | words | 1 |
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;
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;
This was exactly what I needed! Thanks!
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.
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.