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
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;
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?
Month , MM ( member count of the month)
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;
thank you , I will try and update with you !
output need to add ID column:
Output three columns as:
ID, Month, MN,
how to add ID ?
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 |
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.