topic Concatenating values of rows conditionally in SAS Programming

Concatenating values of rows conditionally

soomx — Thu, 25 Apr 2019 21:02:02 GMT

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!

Re: Concatenating values of rows conditionally

novinosrin — Thu, 25 Apr 2019 21:05:41 GMT




 
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;

Re: Concatenating values of rows conditionally

soomx — Thu, 25 Apr 2019 21:12:34 GMT

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

Re: Concatenating values of rows conditionally

novinosrin — Thu, 25 Apr 2019 21:13:50 GMT

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

Rest is a breeze 🙂

Re: Concatenating values of rows conditionally

PGStats — Thu, 25 Apr 2019 21:19:06 GMT

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;