topic Re: Combine multiple observations into one if they have the same Id in SAS Procedures

Combine multiple observations into one if they have the same Id

ZoeyElle — Tue, 23 Aug 2011 07:53:37 GMT

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!

Combine multiple observations into one if they have the same Id

LinusH — Tue, 23 Aug 2011 12:02:46 GMT

Seems like a report requirement to me...?

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

/Linus

Combine multiple observations into one if they have the same Id

FriedEgg — Tue, 23 Aug 2011 15:09:31 GMT

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;

Combine multiple observations into one if they have the same Id

DLing — Tue, 23 Aug 2011 18:32:07 GMT

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.

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

MikeZdeb — Wed, 24 Aug 2011 03:28:07 GMT

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 :obs

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

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

Irfanmohammed — Mon, 30 Jan 2017 21:59:39 GMT

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.

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

mkeintz — Tue, 31 Jan 2017 03:58:06 GMT

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

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

mkeintz — Tue, 31 Jan 2017 04:25:10 GMT

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.

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

Peter_C — Wed, 01 Feb 2017 10:30:25 GMT

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.

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

Peter_C — Wed, 01 Feb 2017 10:31:32 GMT

edited to format code and remove typo(s)

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

Irfanmohammed — Wed, 01 Feb 2017 15:17:10 GMT

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;

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

kmw — Fri, 29 Sep 2017 19:46:52 GMT

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.

http://support.sas.com/kb/24/745.html

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

jsbyxws — Thu, 06 Sep 2018 00:57:44 GMT

Hi Peter,

Thanks for the code! It works and I don't know why. Can you help to explain step by step for your code with following sample data (name have😞

Key Data

A A1

B B1

B B2

The result based on your code would be:

Key Collection Item (I keep it since it is count)

A A1 1

B B1,B2 2

But I am still confused by your "Set Your_data" inside do loop and other steps. Can you show us how observation 1, 2, 3 work in detail?

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

Peter_C — Thu, 06 Sep 2018 08:47:25 GMT

Try the dataset debugger (which now works in EG ).
It allows you to watch values change, as each statement is executed

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

Peter_C — Thu, 06 Sep 2018 08:49:13 GMT

Apple. for typo.... look for the data step debugger
There are a number of great articles in the SAS DUMMY blog.