topic Re: replacing missing date values in a column with new date in SAS Programming

replacing missing date values in a column with new date

radhikaa4 — Mon, 20 Aug 2018 17:19:57 GMT

Hello. I am trying to generate an algorithm that best catches the missing dates and replaces with the expected date if next consequent visit is completed. I would like to use PROC SQL, I am unable to

Here is the sample dataset;

patientid	day	visit_date	expected_visit_date
1234	30		1/30/2017
1234	180	7/29/2017	7/29/2017
1234	365	1/30/2018	1/30/2018
4567	30		1/20/2017
4567	180		7/19/2017
4567	365	1/19/2018	1/20/2018
7890	30		12/3/2016
7890	180	6/1/2017	6/1/2017
7890	365		12/3/2017

New dataset:

patientid	day	visit_date	expected_visit_date	new_visit_date
1234	30		1/30/2017	1/30/2017
1234	180	7/29/2017	7/29/2017	7/29/2017
1234	365	1/30/2018	1/30/2018	1/30/2018
4567	30		1/20/2017	1/20/2017
4567	180		7/19/2017	7/19/2017
4567	365	1/19/2018	1/20/2018	1/19/2018
7890	30		12/3/2016	12/3/2016
7890	180	6/1/2017	6/1/2017	6/1/2017
7890	365		12/3/2017

Re: replacing missing date values in a column with new date

UdayGuntupalli — Mon, 20 Aug 2018 18:28:18 GMT

@radhikaa4,
If you are open to other solutions, I would like to offer the following using a data step.

Data Want; 
       set Have;   /* where Have is your original dataset */
       If Missing(visit_date) Then
          Do;
             new_visit_date  = expected_visit_date; 
          End;
       Else 
          Do; 
              new_visit_date = visit_date; 
          End; 
Run;

The Proc SQL version might look something like this:

Proc Sql;
	Create Table Want As 
		Select *,
		       Case 
		       	    When Missing(visit_date) Then expected_visit_date
		       		Else visit_date
		       	End As new_visit_date
		From Have ;
Quit;

Re: replacing missing date values in a column with new date

Ksharp — Tue, 21 Aug 2018 13:17:05 GMT

proc sql;

select coalesce(visit_date,expected_visit_date) as new_visit_date

from have;

quit;