Concatenating values of rows conditionally

[soomx]

Hi,

I have a data like this:

ID     Date             Trt

1      3/10/2018     AN

1      5/3/2018       BH

1      5/3/2018       AN

1      9/9/2018      CL

2      2/10/2018     AN

3      5/30/2018       CL

3      5/30/2018       TH

3      12/9/2018      AN

4      3/15/2018     GB

4      6/3/2018       BH

4      10/3/2018       AN

I want this:

ID     Date             Trt

1      3/10/2018     AN

1      5/3/2018       BH/AN

1      9/9/2018      CL

2      2/10/2018     AN

3      5/30/2018       CL/TH

3      12/9/2018      AN

4      3/15/2018     GB

4      6/3/2018       BH

4      10/3/2018       AN

Basically, I am combining the TRT that occur on the same dates into one value. I can't seem to do this without combining all the TRT for each ID into one.

Thank you!

[novinosrin · Accepted solution]

 
data have;
input ID     Date :mmddyy10.            Trt $;
format date mmddyy10.;
cards;
1      3/10/2018     AN
1      5/3/2018       BH
1      5/3/2018       AN
1      9/9/2018      CL
2      2/10/2018     AN
3      5/30/2018       CL
3      5/30/2018       TH
3      12/9/2018      AN
4      3/15/2018     GB
4      6/3/2018       BH
4      10/3/2018       AN
;

data want;
do until(last.date);
set have;
by id date;
length want $50;
want=catx('/',want,trt);
end;
run;

[soomx]

Worked perfectly. Thank you so much...can't believe it's so simple!

[novinosrin]

Yes you are right. Just a matter of understanding by group processing in a sorted dataset by id date

Rest is a breeze 🙂

[PGStats]

Use a DO UNTIL loop and the catx function:

data have;
input ID Date:mmddyy. Trt$ ;
format date yymmdd10.;
datalines;
1      3/10/2018     AN
1      5/3/2018       BH
1      5/3/2018       AN
1      9/9/2018      CL
2      2/10/2018     AN
3      5/30/2018       CL
3      5/30/2018       TH
3      12/9/2018      AN
4      3/15/2018     GB
4      6/3/2018       BH
4      10/3/2018       AN
;

data want;
length trts $20;
do until (last.date);
    set have; by ID date;
    trts = catx("/", trts, trt);
    end;
drop trt;
run;

proc print data=want noobs;
run;