Hi there,
I have a log form data set with a column for id, visit type and visit date. I want to create a new variable (0/1) indicating which visits have a date greater than the date for visit type='6' for each ID. So for example, in the simplified data below, I want to the drop the rows with bolded values because the dates for those visits come after the dates for visit type 6.
I thought I could do this with a simple combination of equation and IF/THEN statements, but I'm stumped. Suggestions?
ID | Visit Type | Date |
1 | 3 | 30-Mar |
1 | 6 | 30-Jun |
1 | 9 | 30-Sep |
2 | 0 | 15-Jan |
2 | 3 | 15-Mar |
2 | 6 | 15-Jun |
3 | 3 | 30-Apr |
3 | 6 | 30-Jul |
3 | 8 | 6-Sep |
4 | 6 | 10-Aug |
4 | 7 | 10-Oct |
4 | 12 | 10-Dec |
Thanks!
data want;
merge have have(where=(visit_type=6) rename=(date=visit_type_6_date));
by id;
if date > visit_type_6_date then flag=1; else flag=0;
run;
I'm not sure why you have not bolded the third record, it seems like under the rules you have explained, that one should get dropped as well.
Are your dates true numeric SAS date values, formatted as shown? Or are they text as shown?
Will every ID value have one and only one visit type equal to 6? Could an ID have zero visit type records equal to 6? Could an ID have more than one visit type records equal to 6?
The dates are formatted as yymmdd10
Will every ID value have one and only one visit type equal to 6? Could an ID have zero visit type records equal to 6? Could an ID have more than one visit type records equal to 6?
data want;
merge have have(where=(visit_type=6) rename=(date=visit_type_6_date));
by id;
if date > visit_type_6_date then flag=1; else flag=0;
run;
I'm not sure why you have not bolded the third record, it seems like under the rules you have explained, that one should get dropped as well.
For this solution to work properly, you need to reverse the order in the merge:
data want;
merge have(where=(visit_type=6) rename=(date=visit_type_6_date)) have;
by id;
if date > visit_type_6_date then flag=1; else flag=0;
run;
Otherwise, the values on the first observation for each ID will be wrong.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.