topic Re: Date equations in long form data in SAS Programming

Date equations in long form data

EpiNovice — Tue, 02 Apr 2019 17:46:10 GMT

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!

Re: Date equations in long form data

PaigeMiller — Tue, 02 Apr 2019 17:58:19 GMT

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?

Re: Date equations in long form data

EpiNovice — Tue, 02 Apr 2019 17:57:57 GMT

The dates are formatted as yymmdd10

Re: Date equations in long form data

PaigeMiller — Tue, 02 Apr 2019 17:58:42 GMT

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?

Re: Date equations in long form data

EpiNovice — Tue, 02 Apr 2019 17:59:57 GMT

Each ID only has a single visit type 6.

Re: Date equations in long form data

PaigeMiller — Tue, 02 Apr 2019 18:04:34 GMT

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.

Re: Date equations in long form data

Astounding — Tue, 02 Apr 2019 18:06:07 GMT

Why bother with dates? Would this work;

if VisitType > 6 then delete;

Re: Date equations in long form data

EpiNovice — Tue, 02 Apr 2019 18:06:21 GMT

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

Re: Date equations in long form data

EpiNovice — Tue, 02 Apr 2019 18:17:05 GMT

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

Re: Date equations in long form data

Astounding — Tue, 02 Apr 2019 19:53:03 GMT

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.

Re: Date equations in long form data

EpiNovice — Tue, 02 Apr 2019 20:29:47 GMT

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