DATA Step, Macro, Functions and more

Using the intnx function correctly?

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

Using the intnx function correctly?

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! 

 


Accepted Solutions
Solution
‎12-30-2017 05:51 PM
PROC Star
Posts: 1,805

Re: Using the intnx function correctly?

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

View solution in original post


All Replies
Solution
‎12-30-2017 05:51 PM
PROC Star
Posts: 1,805

Re: Using the intnx function correctly?

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

PROC Star
Posts: 8,164

Re: Using the intnx function correctly?

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

 

Super User
Posts: 23,724

Re: Using the intnx function correctly?

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. 

Esteemed Advisor
Posts: 5,529

Re: Using the intnx function correctly?

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;
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 252 views
  • 3 likes
  • 5 in conversation