Member enrollment month

[JHE]

I have Member ID, efft_date, term_date, 

I like to know members monthly enrolment  count called: Member Month MM

ID	EFF_DT	END_DT
1304	3/2/2019	1/1/2023
1508	2/1/2022	12/31/2023
1907	12/1/2021	8/30/2022

Need to set up flag those members enroll month as 1  then summary month total for three of them , call member month 

[SASJedi]

Please provide a mock-up table or description for your desired output. That is, given the input data you provided, what would the correct result look like? 

[JHE]

Month  ,  MM ( member count of the month) 

[FreelanceReinh]

Hi @JHE,

Try this:

data have;
input id (eff_dt end_dt) (:mmddyy.);
format eff_dt end_dt date9.;
cards;
1304 3/2/2019  1/1/2023
1508 2/1/2022  12/31/2023
1907 12/1/2021 8/30/2022
;

proc sql noprint;
select min(eff_dt), intck('month', min(eff_dt),max(end_dt)) into :m1 trimmed, :nm trimmed
from have;
quit;

data want;
set have end=last;
array _m[0:&nm] _temporary_;
do _i=intck('month',&m1,eff_dt) to intck('month',&m1,end_dt);
  _m[_i]+1;
end;
if last then do _i=0 to dim(_m)-1;
  Month=intnx('month',&m1,_i);
  MM=_m[_i];
  output;
end;
format Month yymmd7.;
keep Month MM;
run;

[JHE]

thank you , I will try and update with you !

[JHE]

output need to add ID column: 

Output three columns as:
ID, Month, MN, 

how to add ID ? 

[JHE]

Question and Answer wanted should be as :

IN PUT
ID	EFF_DT	END_DT
1304	1/1/2019	1/1/2023
1398	1/1/2019	12/31/2020
OUT PUT 1
ID	MONTH	MM
1302	2019-01	1
1302	2019-02	1	….
1302	2019-03	1
1398	2019-01	1
1398	2019-02	1
1398	2019-03	1
Final from above to have final result as:
Output 2
MONTH	MM
2019-01	2
2019-02	2
2019-03	2

[FreelanceReinh]

Output 2 is what the code in my first reply produces. For output 1 you can use:

data want1;
set have;
format Month yymmd7.;
retain MM 1;
do _i=0 to intck('month',eff_dt,end_dt);
  Month=intnx('month',eff_dt,_i);
  output;
end;
keep ID Month MM;
run;

[Tom · Accepted solution]

I cannot figure out what you mean.

So let's posit a guess:

1) Find the number of months that the START/STOP dates cover.

2) Get the grand total across all subjects.

First let's convert your listing into an actual dataset.  Looks like your date strings are in MDY order. Let's fix that also so there aren't any ambiguous date strings.

data have;
  input ID :$5. EFF_DT END_DT ;
  informat eff_dt end_dt yymmdd.;
  format eff_dt end_dt yymmdd10.;
cards;
1304 2019-03-02 2023-01-01
1508 2022-02-01 2023-12-31
1907 2021-12-01 2022-08-30
;

You can use INTCK() to find the number of months between the two dates.  You might want to add one if you want an interval that is totally in one month to be larger than zero.

data want;
  set have;
  mm = 1 + intck('month',eff_dt,end_dt);
run;

You can use PROC MEANS to sum them up.

proc means data=want n sum;
  var mm;
run;