I have a data set with DMRN and date of the visit (DX_Date). I would like to generate the the start and stop time or T1 and T2 based on each visit date. Could you please help me with the code?
Here is how the data look like:
DMRN | DX_DATE |
31 | 09MAY2017 |
31 | 30MAY2017 |
31 | 06APR2018 |
31 | 07FEB2019 |
33 | 02JUN2017 |
33 | 04DEC2017 |
33 | 11JUN2018 |
This is how I want to outcome be like:
DMRN | T1 | T2 |
31 | 09MAY2017 | 30MAY2017 |
31 | 30MAY2017 | 06APR2018 |
31 | 06APR2018 | 07FEB2019 |
33 | 02JUN2017 | 04DEC2017 |
33 | 04DEC2017 | 11JUN2018 |
Here one option.
data have;
input DMRN DX_DATE:date9.;
format dx_date date9.;
datalines;
31 09MAY2017
31 30MAY2017
31 06APR2018
31 07FEB2019
32 01JUN2017
33 02JUN2017
33 04DEC2017
33 11JUN2018
;
data want;
set have;
by dmrn dx_date;
format t1 t2 date9.;
retain t1;
if first.dmrn and last.dmrn then
do;
t1=dx_date;
output;
end;
else if not first.dmrn then
do;
t2=dx_date;
output;
end;
t1=dx_date;
run;
proc print data=want;
run;
Here one option.
data have;
input DMRN DX_DATE:date9.;
format dx_date date9.;
datalines;
31 09MAY2017
31 30MAY2017
31 06APR2018
31 07FEB2019
32 01JUN2017
33 02JUN2017
33 04DEC2017
33 11JUN2018
;
data want;
set have;
by dmrn dx_date;
format t1 t2 date9.;
retain t1;
if first.dmrn and last.dmrn then
do;
t1=dx_date;
output;
end;
else if not first.dmrn then
do;
t2=dx_date;
output;
end;
t1=dx_date;
run;
proc print data=want;
run;
Thank you so much for your help!
Could you also please help me with the code if I want to generate t1 t2 t3 and t4?
The outcome would be:
DMRN | t1 | t2 | t3 | t4 |
31 | 09MAY2017 | 30MAY2017 | 06APR2018 | 07FEB2019 |
33 | 02JUN2017 | 04DEC2017 | 11JUN2018 | . |
@tan-wongv wrote:
Thank you so much for your help!
Could you also please help me with the code if I want to generate t1 t2 t3 and t4?The outcome would be:
DMRN t1 t2 t3 t4 31 09MAY2017 30MAY2017 06APR2018 07FEB2019 33 02JUN2017 04DEC2017 11JUN2018 .
This would do the basic reshaping of the data if the data is at least grouped by DMRN:
proc transpose data=have out=want (drop=_name_) prefix=t ; by dmrn notsorted; var dx_date; run;
You would have to describe the logic why DRMN=32 is not in your want set and apply that logic to the Want to remove it (I might guess if the number of T variables with not missing values (function N) is greater than 1.
Thank you for your guidance! DMRN is like an ID. I don't have all the number since this is the subset from the original dataset.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.