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 |
@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;
proc sql;
select coalesce(visit_date,expected_visit_date) as new_visit_date
from have;
quit;
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.