Desktop productivity for business analysts and programmers

combine variables with same id

Reply
Contributor
Posts: 33

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: 11,107

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

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: 11,107

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;
Ask a Question
Discussion stats
  • 4 replies
  • 545 views
  • 1 like
  • 3 in conversation