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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.