I have a data set contains DMRN, last visit date, the presence of UI, and recorded date of UI.
DMRN |
last_visit |
UI |
recorded_time |
31 |
26AUG2021 |
0 |
06APR2018 |
31 |
26AUG2021 |
0 |
16JAN2020 |
31 |
26AUG2021 |
1 |
4MAY2021 |
33 |
24MAY2022 |
0 |
02MAR2020 |
33 |
24MAY2022 |
0 |
24MAY2022 |
35 |
01DEC2014 |
0 |
25MAR2013 |
I would like to create new_date for each DMRN based on these conditions:
if UI = 1 then new_date = recorded_time
else if UI = 0 then new_date = last_visit
The outcome would be like as follow:
DMRN | last_visit | UI | recorded_time | new_date |
31 | 26AUG2021 | 0 | 06APR2018 | 4MAY2021 |
31 | 26AUG2021 | 0 | 16JAN2020 | 4MAY2021 |
31 | 26AUG2021 | 1 | 4MAY2021 | 4MAY2021 |
33 | 24MAY2022 | 0 |
02MAR2020 |
24MAY2022 |
33 | 24MAY2022 | 0 | 24MAY2022 | 24MAY2022 |
35 | 01DEC2014 | 0 | 25MAR2013 | 01DEC2014 |
Could you please help me with the code? Thank you very much
You seem to have already written the code.
Did you try running it? What happened?
data want;
set have;
if UI = 1 then new_date = recorded_time;
else if UI = 0 then new_date = last_visit;
format new_date date9.;
run;
This code works for those who have UI = 0, new_date is equal to last_visit
However, it did not work for those who have UI = 1, such as DMRN 31. I would like new_date for DMRN31 to be as 4MAY2021 4MAY2021 4MAY2021
DMRN | last_visit | UI | recorded_time | new_date |
31 | 26AUG2021 | 0 | 06APR2018 | 26AUG2021 |
31 | 26AUG2021 | 0 | 16JAN2020 | 26AUG2021 |
31 | 26AUG2021 | 1 | 4MAY2021 | 4MAY2021 |
33 | 24MAY2022 | 0 | 02MAR2020 | 24MAY2022 |
33 | 24MAY2022 | 0 | 24MAY2022 | 24MAY2022 |
35 | 01DEC2014 | 0 | 25MAR2013 | 01DEC2014 |
Works fine.
Are you sure you want the extra IF in there? What value do you want for NEW_VAR when UI is neither 1 nor zero?
data have;
input DMRN $ last_visit :date. UI recorded_time :date.;
format last_visit recorded_time date9.;
cards;
31 26AUG2021 0 06APR2018
31 26AUG2021 0 16JAN2020
31 26AUG2021 1 4MAY2021
31 26AUG2021 1 26MAY2022
33 24MAY2022 0 02MAR2020
33 24MAY2022 0 24MAY2022
35 01DEC2014 0 25MAR2013
35 01DEC2014 1 05JAN2015
;
data want ;
set have;
if ui=1 then new_var = recorded_time;
else new_var = last_visit;
format new_var date9.;
run;
Result
last_ recorded_ Obs DMRN visit UI time new_var 1 31 26AUG2021 0 06APR2018 26AUG2021 2 31 26AUG2021 0 16JAN2020 26AUG2021 3 31 26AUG2021 1 04MAY2021 04MAY2021 4 31 26AUG2021 1 26MAY2022 26MAY2022 5 33 24MAY2022 0 02MAR2020 24MAY2022 6 33 24MAY2022 0 24MAY2022 24MAY2022 7 35 01DEC2014 0 25MAR2013 01DEC2014 8 35 01DEC2014 1 05JAN2015 05JAN2015
Again check that the values of UI are actually integers.
If @Tom 's code doesn't return the desired result then run below and investigate any row that gets written to table Investigate.
data investigate ;
set have;
if ui not in (0,1) then output;
run;
or here another way to check what values you've actually got in UI.
proc freq data=have;
table ui;
run;
proc contents data=have(keep=ui);
run;quit;
Hello @tan-wongv
If you want the outcome as you have shown, then you would need to modify @Tom 's code as follow
data have;
input DMRN $ last_visit :date. UI recorded_time :date.;
format last_visit recorded_time date9.;
cards;
31 26AUG2021 0 06APR2018
31 26AUG2021 0 16JAN2020
31 26AUG2021 1 4MAY2021
31 26AUG2021 1 26MAY2022
33 24MAY2022 0 02MAR2020
33 24MAY2022 0 24MAY2022
35 01DEC2014 0 25MAR2013
35 01DEC2014 1 05JAN2015
;
data want ;
set have;
if ui=1 then new_date = recorded_time;
else if ui=0 and DMRN=31 then new_date="04MAY2021"d;
else new_date = last_visit;
format new_date date9.;
run;
The result would be as you wished
Is it guaranteed that each dmrn has 0 or 1 obs with ui = 1, or could there be more obs with ui = 1?
data ui;
set have(
keep= DMRN recorded_time ui
rename=(recorded_time = new_date)
where=(ui = 1)
);
drop ui;
run;
data want;
set have;
length new_date 8;
format new_date date9.;
if _n_ = 1 then do;
declare hash h(dataset: 'ui');
h.defineKey('dmrn');
h.defineData('new_date');
h.defineDone();
end;
if h.find() ^= 0 then do;
new_date = recorded_time;
end;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.