topic Re: replacing actual date to expected date in SAS Programming

replacing actual date to expected date

radhikaa4 — Mon, 17 Sep 2018 14:54:38 GMT

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

Re: replacing actual date to expected date

RW9 — Mon, 17 Sep 2018 15:12:59 GMT

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.

Re: replacing actual date to expected date

PGStats — Mon, 17 Sep 2018 20:03:48 GMT

You can use

coalesce(date_1, case when date_2 is missing then . else date_2_expected end) as date_1 format=yymmdd10.,