- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;