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
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.