Solved
Contributor
Posts: 48

# 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

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.

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.