Hi AviS,
Here is a possible solution to your problem:
data A;
length ID $2 Y2017-Y2019 $10;
input ID 1-2 Y2017 4-10 Y2018 13-18 Y2019 20-25;
datalines;
a a2, a3 a2,a3 a4
b a2 a4
c a1, a2 a2,a3 a4,a3
;
data B (keep=ID VAL);
set A;
length ALL $200 VAL $10;
ALL = catx(',',Y2017,Y2018,Y2019);
do i=1 to countw(ALL,',');
VAL = strip(scan(ALL,i,','));
output;
end;
run;
proc sort data=B out=C nodupkey;
by ID VAL;
run;
data D (drop=VAL);
merge A C;
by ID;
length FINAL $200;
retain FINAL;
if first.ID then FINAL = '';
FINAL = catx(', ', FINAL,VAL);
if last.ID then output;
run;
Run this code to make sure it works for you.
Is that what you want?
Best,
... View more