Help using Base SAS procedures

Combine multiple observations into one if they have the same Id

Reply
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!

Super User
Posts: 5,257

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
Trusted Advisor
Posts: 1,300

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 dataSmiley Happy;

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 Smiley Surprisedbs

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

Valued Guide
Posts: 797

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.

Valued Guide
Posts: 797

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,175

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,175

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

edited to format code and remove typo(s)
Ask a Question
Discussion stats
  • 10 replies
  • 8308 views
  • 0 likes
  • 8 in conversation