topic Re: Member enrollment month in SAS Programming

Member enrollment month

JHE — Wed, 17 Jan 2024 16:41:54 GMT

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

Re: Member enrollment month

SASJedi — Wed, 17 Jan 2024 17:00:40 GMT

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?

Re: Member enrollment month

JHE — Wed, 17 Jan 2024 17:09:50 GMT

Month , MM ( member count of the month)

Re: Member enrollment month

FreelanceReinh — Wed, 17 Jan 2024 17:14:46 GMT

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;

Re: Member enrollment month

JHE — Wed, 17 Jan 2024 17:22:31 GMT

thank you , I will try and update with you !

Re: Member enrollment month

Tom — Wed, 17 Jan 2024 17:55:20 GMT

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;

Re: Member enrollment month

JHE — Wed, 17 Jan 2024 18:20:47 GMT

output need to add ID column:

Output three columns as:
ID, Month, MN,

how to add ID ?

Re: Member enrollment month

JHE — Wed, 17 Jan 2024 18:30:56 GMT

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

Re: Member enrollment month

FreelanceReinh — Wed, 17 Jan 2024 23:13:40 GMT

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;