Using the intnx function correctly?

[cdubs]

I am trying to get individuals that have continuous eligibility +/- 1 year before an index_dt. 

The two datasets I'm merging are:

• compiled_ip_index which contains enrolid-year combinations and a corresponding index_dt
• ce_merged_1014 which contains enrolid and continuous enrollment start date and end date

My current code is: 

proc sql; 

create table final_elig_ip as 
select a.*, b.*
from compiled_ip_index as a
inner join summary.ce_merged_1014 as b
on a.enrolid = b.enrolid and a.year = b.year and intnx ('day', b.enroll_start_dt, a.index_dt)>=365 and intnx ('day', a.index_dt, b.enroll_end_dt)>=365;

quit;

 But this ends up yielding a final_elig_ip where index date is often within 365 days of the enrollment start date. 

What went wrong? 

Thank you! 

[novinosrin · Accepted solution]

I think it sounds like you need the days interval and not the increment of intervals if I understand you correctly. You would probably need to look at Intck function instead of intnx

[art297]

Question about your specs. You said "I am trying to get individuals that have continuous eligibility +/- 1 year before an index_dt. "

The "+/- 1 year before" raises a question. Do you mean "at least one year before or after" or something else?

If you mean one year before or after, then why not simply use:

abs(b.enroll_start_dt-a.index_dt)>=365

Art, CEO, AnalystFinder.com

[Reeza]

INTNX returns a date the specified intervals, ie date at which you turn 30 

INTCK returns the intervals between two dates, ie number of days, months or years between two dates. 

Dates are also numeric so you so you can subtract them. Be specific in your definitions though, 365 days is not the same as a year or 12 months. 

[PGStats]

I think what you need is:

proc sql; 
create table final_elig_ip as 
select 
    a.*, 
    b.*
from 
    compiled_ip_index as a inner join 
    summary.ce_merged_1014 as b
    on 
        a.enrolid = b.enrolid and 
        a.year = b.year and 
        abs(intck('year', b.enroll_start_dt, a.index_dt, "CONTINUOUS")) >= 1;
quit;