topic Re: combine variables with same id in SAS Enterprise Guide

combine variables with same id

user24 — Fri, 23 Oct 2015 19:33:17 GMT

I have a table as below

id number

2 12

3 45

3 74

5 33

5 12

5 15

What i need is

id number

2 12

3 45-74

5 33-12-15

Re: combine variables with same id

ballardw — Fri, 23 Oct 2015 19:31:50 GMT

Are you looking for the calculation 45-74 or a character value "45-74"? Is Number currenlty numeric or character?

What if there are 3 or more records with the same value of Id?

Re: combine variables with same id

user24 — Fri, 23 Oct 2015 19:34:29 GMT

Sorry it wasnot clear. I updated tables. It is character values not calculation. And yes if i have more than 2 it will be same like 12-15-17-13. Thank you

Re: combine variables with same id

kannand — Fri, 23 Oct 2015 20:50:25 GMT

Here you go..

data in;infile datalines dlm='09'x;
input id:$2. num: $2.;
datalines;
2 12
3 45
3 74
5 33
5 12
5 15
;RUN;
proc sort;by id;run;
data in(keep=id new_id rename=(new_id=num));
set in;by id;
length new_id $3000.;
length final_id $3000.;
retain new_id;
if first.id then new_id = num; else new_id=left(trim(new_id))|| '-' || num;
if last.id then do;final_id=left(trim(new_id));output;end;
run;

Hope this helps...!!!

Re: combine variables with same id

ballardw — Fri, 23 Oct 2015 20:54:27 GMT

Something like this maybe what you are looking for. You imply that the data is in order so I will assume it is sorted by the ID.

data want;
   set have;
   by id;
   length numcombined $ 50 ; /* this needs to be long enough to hold the longest combination plus the -*/
   if first.id then numcombined=number;
   else numcombined = catx('-',numcombined,number);
   if last.id;
run;