topic Re: Merge or cancandinate the row with same ID in SAS Programming

Merge or cancandinate the row with same ID

JHE — Thu, 26 Jul 2018 21:09:19 GMT

Need advise:

I have data as this:

Month	ID	Code	Count
201806	30712566	XB3	2
201806	30712566	Z99	1

I need to have result as this:

Month	ID	Code	Count
201806	30712566	XB3,Z99	3

Please advise and help

Thank you

Re: Merge or cancandinate the row with same ID

novinosrin — Thu, 26 Jul 2018 21:40:14 GMT

data have;
infile cards truncover;
input Month	ID	Code $	Count;
cards;
201806	30712566	XB3	2
201806	30712566	Z99	1
;
data want;
set have;
by month id;
length _code $10;
retain _code;
if first.id then do; _code=code;_count=count;end;
else do; _code=catx(',',_code,code);_count+count;end;
if last.id;
run;

Re: Merge or cancandinate the row with same ID

ballardw — Thu, 26 Jul 2018 22:01:35 GMT

Make sure the length of the _CODE variable is long enough to hold the maximum number of codes you expect and include extra characters for the commas.

Re: Merge or cancandinate the row with same ID

novinosrin — Thu, 26 Jul 2018 22:02:24 GMT

I agree