Month counts in certain date period

[angel302]

I am trying to evaluate member's plan eligibility as of the 15th of each month for this population in 2014. Below is my dataset, called Member:

MemberID       Elig_Start           Elig_End

            1               1/1/2014          9/25/2014
            2               1/1/2014         12/31/9999
            3               1/1/2012          2/31/2013

The output should be something like this:

MemberID       Elig_Start           Elig_End                Elig_Month

            1               1/1/2014          9/25/2014                     9

            2               1/1/2014         12/31/9999                   12 
            3               1/1/2012          2/31/2013                      0 (or missing)

Anybody has idea? Thank you!

[PaigeMiller]

I don't understand how the last row winds up as a zero. Could you please explain?

I don't understand how you can have a date of 2/31/13.

[angel302]

@PaigeMiller 

It's more like a historical enrollee lists. Since the purpose is to identify member's 2014 eligibility, the eligibility of enrollment outside this date range should return to 0.   

[PaigeMiller]

---

@angel302 wrote:

@PaigeMiller 

It's more like a historical enrollee lists. Since the purpose is to identify member's 2014 eligibility, the eligibility of enrollment outside this date range should return to 0.   

---

Okay, but this should have been stated at the beginning.

[FreelanceReinh · Accepted solution]

Hi @angel302,

Try this:

data have;
input MemberID (Elig_Start Elig_End) (:mmddyy.);
cards;
1 1/1/2014 9/25/2014
2 1/1/2014 12/31/9999
3 1/1/2012 12/31/2013
;

%let yr=2014;

data want;
set have;
Elig_Month=0;       
do _n_=max(elig_start,"15JAN&yr"d) to min(elig_end,"15DEC&yr"d);
  elig_month+day(_n_)=15;
end;
run;

It's possible to solve this using INTCK/INTNX, but I think the above is more transparent.

[hashman]

@FreelanceReinh:

Amazing that you've been able to discern what the OP wanted. I can see it from your resulting code but kill me if I can see it from the OP's description of the task ;).

Kind regards

Paul D. 

[angel302]

@FreelanceReinh Tried it and it works perfectly. Thank you so much for your assist!