Solved
New Contributor
Posts: 3

# How to concatenate multiple rows into one row by ID number

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

Accepted Solutions
Solution
‎07-18-2016 11:50 AM
Super User
Posts: 10,784

## Re: How to concatenate multiple rows into one row by ID number

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;``````

All Replies
Super User
Posts: 5,881

## Re: How to concatenate multiple rows into one row by ID number

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
Solution
‎07-18-2016 11:50 AM
Super User
Posts: 10,784

## Re: How to concatenate multiple rows into one row by ID number

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;``````

New Contributor
Posts: 3

## Re: How to concatenate multiple rows into one row by ID number

This was exactly what I needed!  Thanks!

Super User
Posts: 9,599

## Re: How to concatenate multiple rows into one row by ID number

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.

New Contributor
Posts: 3

## Re: How to concatenate multiple rows into one row by ID number

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

Super User
Posts: 9,599

## Re: How to concatenate multiple rows into one row by ID number

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.

☑ This topic is solved.