## Combine multiple observations into one if they have the same Id

Solved
Occasional Contributor
Posts: 10

# Combine multiple observations into one if they have the same Id

Good day, everyone.

Here's my sample data:

Key.            Data

Abc123.      077123

Abc123.      098546-

Abc123.      875433

Def123.       345423

Def123.       344565x

I need to get result as follows:

Abc123.       077123

098546-

875433

Def123.        345423

344565x

The result should only be 2 observations. 1st observation is with key Abc123 . 2nd observation is with key Def123.

Notice the data column has been combined into one variable and displayed below one another....but in a single observstion.

How can i do the above. Thanks!

Accepted Solutions
Solution
‎09-29-2017 03:47 PM
SAS Employee
Posts: 11

## Re: Combine multiple observations into one if they have the same Id

If you want to collapse observations of the same BY value into a single observation, you can use the sample code in this SAS note.

All Replies
Super User
Posts: 5,876

## Combine multiple observations into one if they have the same Id

Seems like a report requirement to me...?

Take a look at PROC PRINT with BY and ID statements.

/Linus

Data never sleeps
Posts: 1,318

## Combine multiple observations into one if they have the same Id

data input;

input key \$ data \$;

cards;

Abc123.      077123

Abc123.      098546-

Abc123.      875433

Def123.      345423

Def123.      344565x

;

run;

proc sort data=input; by key; run;

/* Option 1 */

proc print data=input noobs; by key; id key; run;

/* Option 2 */

data output;

length all_data \$256;

set input;

by key;

retain all_data;

if first.key then all_data=data;

all_data=catx(' ', all_data, data);

if last.key then output;

run;

proc print data=output; run;

Frequent Contributor
Posts: 104

## Combine multiple observations into one if they have the same Id

May I humbly suggest this is an ill-posed question, not in the SAS way of looking at the problem.  The notion that "1 field" spans "3 lines" just doesn't exist in SAS.  If it is merely for reporting, then as other posters have suggested, there are multiple ways to have it printed/displayed that way.

Valued Guide
Posts: 765

## Re: Combine multiple observations into one if they have the same Id

Hi ... here's another idea.  Use PROC SUMMARY to aggregate the values within a vakue of KEY, then use a data step to

concatenate the values into one variable.  The CLASS variable avoids the sort.

data input;

input key \$ data \$;

cards;

Abc123.      077123

Abc123.      098546-

Abc123.      875433

Def123.      345423

Def123.      344565x

;

run;

proc summary nway data=input missing;

class key;

output out=new (drop=_type_ _freq_) idgroup(out[3](data)=);

run;

data new;

set new;

newvar = catx(' ', of data;

keep key newvar;

run;

If you are not sure of the number of observations within each key, you can use ...

proc sql noprint;

select max(obs) into bs

from (select count(*) as obs from input group by key);

quit;

prior to PROC SUMMARY and substitute [&obs] for the [3] used above;

fyi ... "Transposing Data Using PROC SUMMARY'S IDGROUP Option"

http://www.lexjansen.com/pharmasug/2009/tt/tt08.pdf

Posts: 1,337

## Re: Combine multiple observations into one if they have the same Id

If you don't know the size of the largest group, you could also just put in a number (say 100) that you know is larger than the size of any possible key group.

New Contributor
Posts: 4

## Re: Combine multiple observations into one if they have the same Id

data input;

input key \$ data \$;

cards;

Abc123.      077123

Abc123.      098546-

Abc123.      875433

Def123.      345423

Def123.      344565x

;

run;

Proc sort data=input nodupkey out=Input1;

by key;

run;

this will delet the number of dulicate observation.

Posts: 1,337

## Re: Combine multiple observations into one if they have the same Id

But it will also lose the data components from the 2nd, 3rd, etc records for each key.  The OP wants to keep them.

New Contributor
Posts: 4

## Re: Combine multiple observations into one if they have the same Id

if this the case then you will have to creat a new data set by copying this data set and give an option of first.(variable) then output=(new variable); run;

Valued Guide
Posts: 2,191

## Re: Combine multiple observations into one if they have the same Id

[ Edited ]

assuming your data are in KEY order, you could write this short data step

``````data out1 ( compress= yes  keep= key collection ) ;
retain KEY ;
length collection \$10000 ;
Do item=1 by 1 until( last.KEY ) ;
set your_data ;
by KEY ;
collection = catX( ',', collection, data ) ;
End ;
run ;``````

That seems likely to provide what is wanted but without arranging the comma-separated data in COLLECTION into a column

Perhaps the separator (in the catx() function call) should be '0A'x instead of ','.
Then in excel the collection would / might appear on multiple lines within the cell.

Valued Guide
Posts: 2,191

## Re: Combine multiple observations into one if they have the same Id

edited to format code and remove typo(s)
Solution
‎09-29-2017 03:47 PM
SAS Employee
Posts: 11

## Re: Combine multiple observations into one if they have the same Id

If you want to collapse observations of the same BY value into a single observation, you can use the sample code in this SAS note.

☑ This topic is solved.