Date equations in long form data

[EpiNovice]

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!

[PaigeMiller]

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?

[EpiNovice]

The dates are formatted as yymmdd10

[PaigeMiller]

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?

[EpiNovice]

Each ID only has a single visit type 6.

[PaigeMiller · Accepted solution]

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.

[EpiNovice]

It should have been bolded, thank you for pointing that out.

[Astounding]

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. 

[EpiNovice]

Thank you, I was wondering why there was something wrong with my new dataset.

[Astounding]

Why bother with dates? Would this work;

if VisitType > 6 then delete;

[EpiNovice]

Unfortunately no. The visit types are actually longer names, I just shortened them for simplicity.