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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.