sas query

[rakeshvvv]

I have dataset with Three Variables ID, VISIT, and DATE. I would like to write a query in such a way that, we have to create 4 th variable by concatenate all of those VISIT and date in one string for the id as one observation.

Below are the sample sample data.

140020001        CYCLE 6           2014-12-02

140020001        CYCLE 6           2014-12-03

140020001        CYCLE 6           2014-12-04

220030001        CYCLE 2           2015-01-13

220030001        CYCLE 2           2015-01-14

220030001        CYCLE 2           2015-01-15

220030001        CYCLE 3           2015-02-03

220030001        CYCLE 3           2015-02-04

220030001        CYCLE 3           2015-02-05

220030001        CYCLE 4           2015-02-24

The output should look like this….

140020001        CYCLE 6:  2014-12-02, 2014-12-03, 2014-12-04

220030001        CYCLE 2: 2015-01-13, 2015-01-14, 2015-01-15, CYCLE3: 2015-02-03, 2015-02-04, 2015-02-05, CYCLE4:2015-02-24        

Thanks

[spg]

You can try this:

PROC TRANSPOSE DATA=have OUT=need (drop=_NAME_) ;

BY ID VISIT;

VAR DATE;

RUN;

DATA need1;

SET need;

put VISIT_DATE $256.;

VISIT_DATE=catx('-',VISIT,putn(COL1,'mmddyy10.'),putn(COL2,'mmddyy10.'), putn(COL3,'mmddyy10.'));

RUN;

[Haikuo · Accepted solution]

A nested DOW should do:

data have;

     input id:$10. visit :&$10. date:$10.;

     cards;

140020001        CYCLE 6           2014-12-02

140020001        CYCLE 6           2014-12-03

140020001        CYCLE 6           2014-12-04

220030001        CYCLE 2           2015-01-13

220030001        CYCLE 2           2015-01-14

220030001        CYCLE 2           2015-01-15

220030001        CYCLE 3           2015-02-03

220030001        CYCLE 3           2015-02-04

220030001        CYCLE 3           2015-02-05

220030001        CYCLE 4           2015-02-24

;

data want;

     do until (last.id);

           do until (last.visit);

                set have;

                by id visit;

                length string $ 200;

                if first.visit then

                     string=catx(',', string,catx(':',visit,date));

                else string=catx(', ',string,date);

           end;

     end;

     keep id string;

run;