Span Months to Next Effective Date

[suncawy]

Hello,

  In need of help to record months for each effective date of eligibility until a change in their coverage.  So, I need to expand so that each month that they are in a specific insurance code, then they change the insurance code and span the months from when they changed their insurance code.

This is the data that I have:

plan_eff_dt	plan_term_dt	ID	INS_CD
1/1/2022	12/30/2022	3761	513
8/1/2022	12/30/2022	3761	600
1/1/2023	12/30/2023	3761	600
7/1/2023	12/30/2023	3761	513

The data that I want:

active_mbr_dt	plan_eff_dt	plan_term_dt	ID	INS_CD
1/1/2022	1/1/2022	12/30/2022	3761	513
2/1/2022	1/1/2022	12/30/2022	3761	513
3/1/2022	1/1/2022	12/30/2022	3761	513
4/1/2022	1/1/2022	12/30/2022	3761	513
5/1/2022	1/1/2022	12/30/2022	3761	513
6/1/2022	1/1/2022	12/30/2022	3761	513
7/1/2022	1/1/2022	12/30/2022	3761	513
8/1/2022	8/1/2022	12/30/2022	3761	600
9/1/2022	8/1/2022	12/30/2022	3761	600
10/1/2022	8/1/2022	12/30/2022	3761	600
11/1/2022	8/1/2022	12/30/2022	3761	600
12/1/2022	8/1/2022	12/30/2022	3761	600
1/1/2023	1/1/2023	12/30/2023	3761	600
2/1/2023	1/1/2023	12/30/2023	3761	600
3/1/2023	1/1/2023	12/30/2023	3761	600
4/1/2023	1/1/2023	12/30/2023	3761	600
5/1/2023	1/1/2023	12/30/2023	3761	600
6/1/2023	1/1/2023	12/30/2023	3761	600
7/1/2023	7/1/2023	12/30/2023	3761	513
8/1/2023	7/1/2023	12/30/2023	3761	513
9/1/2023	7/1/2023	12/30/2023	3761	513
10/1/2023	7/1/2023	12/30/2023	3761	513
11/1/2023	7/1/2023	12/30/2023	3761	513
12/1/2023	7/1/2023	12/30/2023	3761	513

[Patrick · Accepted solution]

Here one way how to go about this. Amended version now also with the logic to create rows until December of the year with the max date from source.

data have;
  infile datalines truncover dlm=' ';
  input plan_eff_dt:mmddyy10. plan_term_dt:mmddyy10. ID INS_CD;
  format plan_eff_dt plan_term_dt date9.;
  datalines;
1/1/2022 12/30/2022 3761 513
8/1/2022 12/30/2022 3761 600
1/1/2023 12/30/2023 3761 600
7/1/2023 12/30/2023 3761 513
;

data want(drop=_:);
  set have;
  by id;
  output;

  if not last.id then
    do;
      _point=_n_+1;
      set have(keep=plan_eff_dt rename=(plan_eff_dt=_next_plan_eff_dt)) point=_point;

      plan_eff_dt=intnx('month',plan_eff_dt,1,'b');
      do while(plan_eff_dt<_next_plan_eff_dt);
        output;
        plan_eff_dt=intnx('month',plan_eff_dt,1,'b');
      end;
    end;
  else
  if last.id then
    do;
      /* create rows until December of max date in source */
      _stop_dt=intnx('year',plan_eff_dt,0,'e');
      plan_eff_dt=intnx('month',plan_eff_dt,1,'b');
      do while(plan_eff_dt<_stop_dt);
        output;
        plan_eff_dt=intnx('month',plan_eff_dt,1,'b');
      end;
    end;
run;

proc print data=want;
run;

[suncawy]

This works, Thank you !

[Ksharp]

data have;
  infile datalines truncover dlm=' ';
  input plan_eff_dt:mmddyy10. plan_term_dt:mmddyy10. ID INS_CD;
  format plan_eff_dt plan_term_dt date9.;
  datalines;
1/1/2022 12/30/2022 3761 513
8/1/2022 12/30/2022 3761 600
1/1/2023 12/30/2023 3761 600
7/1/2023 12/30/2023 3761 513
;
data want;
 merge have have(firstobs=2 keep=plan_eff_dt rename=(plan_eff_dt=_plan_eff_dt ));
 active_mbr_dt=plan_eff_dt;output;
 do i=1 to intck('month',plan_eff_dt,ifn(missing(_plan_eff_dt),intnx('month',plan_term_dt,1),_plan_eff_dt))-1;
   active_mbr_dt=intnx('month',plan_eff_dt,i);
   output;
 end;
 drop i _plan_eff_dt ;
 format active_mbr_dt mmddyy10.;
run;

[suncawy]

Thank you, this works too !