BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cdubs
Quartz | Level 8

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! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

4 REPLIES 4
novinosrin
Tourmaline | Level 20

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
Opal | Level 21

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
Super User

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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