topic sas query in SAS Procedures

sas query

rakeshvvv — Fri, 27 Mar 2015 18:14:59 GMT

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

Re: sas query

spg — Fri, 27 Mar 2015 19:57:32 GMT

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;

Re: sas query

Haikuo — Fri, 27 Mar 2015 20:11:59 GMT

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;

keep id string;

run;