Hi
I would like to code the following:
If date 1 is not filled in, but the subsequent date is filled in then use EXPECTED date for that date. Same for others (date 4 is always filled in).
subject_id | date_1 | date_1_expected | date_2 | date_2_expected | date_3 | date_3_expected | date_4 | date_4_expected |
1111 | 1/1/2016 | 1/2/2016 | 1/7/2016 | 1/12/2016 | 1/17/2016 | 1/17/2016 | ||
2222 | 1/5/2017 | 1/9/2018 | 1/10/2017 | 1/15/2017 | 1/20/2017 | 1/20/2017 | ||
3333 | 1/4/2018 | 1/4/2018 | 1/9/2018 | 1/14/2018 | 1/18/2018 | 1/19/2018 |
Right now, I have the following code:
proc sql;
select coalesce(date_1,date_1_expected) as date_1_new
from dataset;
quit;
but I would like to add in the constraint that next date is filled
Not tested as no test data in the form of a datastep:
data want; set have; array d{4} date_:; array d_e{4} date_1_expected date_2_expected date_3_expected date_4_expected; do i=1 to 3; if d{i}=. and d{i+1} ne . then d{i}=d_e{i}; end; run;
Note, that this isn't run for the last element as no +1. Also note, you have to type in the variables for the expected as you have made it as hard as possible by the naming convetion. Use prefixed variable names to make your life easier like with date_X.
You can use
coalesce(date_1, case when date_2 is missing then . else date_2_expected end) as date_1 format=yymmdd10.,
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.