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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.