Contributor
Posts: 34

combine variables with same id

[ Edited ]

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

Super User
Posts: 13,906

Re: combine variables with same id

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?

Contributor
Posts: 34

Re: combine variables with same id

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

Regular Contributor
Posts: 161

Re: combine variables with same id

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

Kannan Deivasigamani
Super User
Posts: 13,906

Re: combine variables with same id

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;``````
Discussion stats
• 4 replies
• 686 views
• 1 like
• 3 in conversation