Help using Base SAS procedures

sas query

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

sas query

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



Accepted Solutions
Solution
‎03-27-2015 04:12 PM
Respected Advisor
Posts: 3,156

Re: sas query

Posted in reply to rakeshvvv

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;

View solution in original post


All Replies
Contributor spg
Contributor
Posts: 61

Re: sas query

Posted in reply to rakeshvvv

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;

Solution
‎03-27-2015 04:12 PM
Respected Advisor
Posts: 3,156

Re: sas query

Posted in reply to rakeshvvv

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 201 views
  • 3 likes
  • 3 in conversation