Combine multiple observations into one if they have the same Id

[ZoeyElle]

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!

[LinusH]

Seems like a report requirement to me...?

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

/Linus

[FriedEgg]

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;

[DLing]

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.

[MikeZdeb]

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

[mkeintz]

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.

[Irfanmohammed]

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.

[mkeintz]

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

[Irfanmohammed]

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;

[Peter_C]

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.

[Peter_C]

edited to format code and remove typo(s)

[kmw · Accepted solution]

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

[jsbyxws]

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?

[Peter_C]

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

[Peter_C]

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