Calculate number of days between 2 date variables

[DeepakSwain]

Hi there, 

I want to calculate number of days from the last treatment whenever a patient visits provided patient is not receiving treatment with every visit. 

Thank you in advance for your kind reply. 

data have;
  length visit_date treatment_date $8;
  id=101; visit_date='20220101' ; treatment_date= '20220110';output;
  id=101; visit_date='20220111' ; treatment_date= '        ';output;
  id=101; visit_date='20220115' ; treatment_date= '20220120';output;
  id=101; visit_date='20220121' ; treatment_date= '20220130';output;
  id=102; visit_date='20220101' ; treatment_date= '20220110';output;
  id=102; visit_date='20220111' ; treatment_date= '        ';output;
  id=102; visit_date='20220121' ; treatment_date= '        ';output;
  id=103; visit_date='20220101' ; treatment_date= '        ';output;
  id=103; visit_date='20220111' ; treatment_date= '2022020';output;
  id=103; visit_date='20220121' ; treatment_date= '        ';output;
  id=103; visit_date='20220122' ; treatment_date= '        ';output;
  id=103; visit_date='20220123' ; treatment_date= '        ';output;
  id=103; visit_date='20220124' ; treatment_date= '        ';output;
  id=103; visit_date='20220125' ; treatment_date= '20220127';output;
  id=103; visit_date='20220128' ; treatment_date= '        ';output;
run;



data want;
  length visit_date treatment_date $8   day_before_last_treatment   3.;
  id=101; visit_date='20220101' ; treatment_date= '20220110';day_before_last_treatment=.;output;
  id=101; visit_date='20220111' ; treatment_date= '        ';day_before_last_treatment=1;output;
  id=101; visit_date='20220115' ; treatment_date= '20220120';day_before_last_treatment=5;output;
  id=101; visit_date='20220121' ; treatment_date= '20220130';day_before_last_treatment=1;output;
  id=102; visit_date='20220101' ; treatment_date= '20220110';day_before_last_treatment=.;output;
  id=102; visit_date='20220111' ; treatment_date= '        ';day_before_last_treatment=1;output;
  id=102; visit_date='20220121' ; treatment_date= '        ';day_before_last_treatment=10;output;
  id=103; visit_date='20220101' ; treatment_date= '        ';day_before_last_treatment=.;output;
  id=103; visit_date='20220111' ; treatment_date= '2022020';day_before_last_treatment=.;output;
  id=103; visit_date='20220121' ; treatment_date= '        ';day_before_last_treatment=1;output;
  id=103; visit_date='20220122' ; treatment_date= '        ';day_before_last_treatment=2;output;
  id=103; visit_date='20220123' ; treatment_date= '        ';day_before_last_treatment=3;output;
  id=103; visit_date='20220124' ; treatment_date= '        ';day_before_last_treatment=4;output;
  id=103; visit_date='20220125' ; treatment_date= '20220127';day_before_last_treatment=5;output;
  id=103; visit_date='20220128' ; treatment_date= '        ';day_before_last_treatment=1;output;
run;

Thanks, 

[PaigeMiller · Accepted solution]

For this to work, you have to convert the character strings to actual SAS date value (which character strings are not). You do this via the INPUT function.

data want;
    set have;
    retain last_treatment_date;
    by id;
    visit_date1=input(visit_date,yymmdd8.);
    treatment_date1=input(treatment_date,yymmdd8.);
    if not first.id then day_before_last_treatment=visit_date1-last_treatment_date;
    if first.id then last_treatment_date=.;
    if not missing(treatment_date1) then last_treatment_date=treatment_date1;
run;

May I also make a request, @DeepakSwain . We shouldn't have to spend 10 minutes trying to figure out the logic you are using here. You need to explain in detail, rather than have us figure out how the numbers are calculated. This will help all of us in the future.