topic Re: Month counts in certain date period in SAS Programming

Month counts in certain date period

angel302 — Sun, 06 Oct 2019 07:24:57 GMT

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!

Re: Month counts in certain date period

PaigeMiller — Sun, 06 Oct 2019 11:15:53 GMT

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.

Re: Month counts in certain date period

FreelanceReinh — Sun, 06 Oct 2019 12:06:28 GMT

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.

Re: Month counts in certain date period

hashman — Sun, 06 Oct 2019 16:33:58 GMT

@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.

Re: Month counts in certain date period

angel302 — Sun, 06 Oct 2019 16:47:44 GMT

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

Re: Month counts in certain date period

angel302 — Sun, 06 Oct 2019 16:50:38 GMT

@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.

Re: Month counts in certain date period

PaigeMiller — Sun, 06 Oct 2019 20:44:20 GMT

@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.