Assign values by rolling month

[Solph]

I've data for evaluation of the effectiveness of a program comparing service utilization before and after enrollment to the intervention program. The utilization is not point in time (e.g. visits), but date range (e.g. length of stay in hospital). So there are intervention program start date and end date, and service utilization start date and end date. I need to allocate length of days prior and after the intervention program by the rolling month. How do I do it? Anywhere to start, programming-wise? 

Here are a few examples of cases with pre-intervention hospital stay and post-intervention hospital stay, in the order of 1) cases with pre-intervention hospital stay, 2) "want" data for it, 3) post-intervention cases, and 4) "want" data for it. The analysis isn't at the individual level but aggregation by the rolling month of intervention program start date. The want data can be in vertical format and I'll just transpose later.

- For the pre cases, I kept only cases with prior admission within 12 months only, so the length of stay should be all allocated or accounted for (PreMon1-PreMon12). 

- For the post cases, I only need the assignment for 12 months (PostMon1-PostMon12). But it's OK to have all days accounted for and I'll just write a code to remove those after the 13th month.

- The pre- and post-cases can be in one program. I thought i would easier to demonstrate.

Re month of 30 or 31 days, etc. I'm not too picky about the precision. It's possible to have hospital stay end date the same as the intervention program start date. They should be assigned to the previous month.

The "want" results might not a couple days off (e.g. 30 vs 31). It's just to give you an idea.

Thanks in advance for the help. 

data have; format id f1.0 PreProg_SD PreProg_ED Prog_SD Prog_ED yymmdd10.;
input @1 ID @3 PreProg_SD MMDDYY10. @14 PreProg_ED MMDDYY10. @25 Prog_SD MMDDYY10. @36 Prog_ED MMDDYY10.; 
datalines;
1 01/18/2014 12/18/2014 12/18/2014 01/04/2017
2 03/19/2013 02/05/2014 02/18/2014 12/17/2014
3 01/01/2014 05/13/2014 09/08/2014 10/08/2015
4 01/26/2016 02/03/2016 03/03/2016 09/20/2016
5 01/11/2016 01/19/2016 01/19/2016 12/31/2016
6 08/10/2014 08/14/2014 08/14/2014 07/31/2017
;
proc print; run;

ID	PreProg_SD	PreProg_ED	Prog_SD	Prog_ED	LOS_period	prevmon1	prevmon2	prevmon3	prevmon4	prevmon5	prevmon6	prevmon7	prevmon8	prevmon9	prevmon10	prevmon11	prevmon12
1	01/18/2014	12/18/2014	12/18/2014	01/04/2017	334	31	31	30	30	31	30	31	30	30	30	30
2	03/19/2013	02/05/2014	02/18/2014	12/17/2014	323	18	30	31	31	30	30	30	31	30	31	30
3	01/01/2014	05/13/2014	09/08/2014	10/08/2015	132				5	30	30	30	30	7
4	01/26/2016	02/03/2016	03/03/2016	09/20/2016	8		8
5	01/11/2016	01/19/2016	01/19/2016	12/31/2016	8	8
6	08/10/2014	08/14/2014	08/14/2014	07/31/2017	4	4

Post-intervention program service utilization cases

data have; format id f1.0 PostProg_SD PostProg_ED Prog_SD Prog_ED yymmdd10.;
input @1 ID @@3 PostProg_SD  MMDDYY10. @15 PostProg_ED MMDDYY10. @26 Prog_SD MMDDYY10. @37 Prog_ED MMDDYY10.; 
datalines;
11 03/02/2016 03/02/2017 05/29/2014 04/30/
12 04/21/2014 04/17/2015 09/23/2013 08/31/2014
13 01/01/2015 02/28/2015 10/01/2014 09/30/2015
14 03/24/2014 03/28/2014 01/13/2014 10/10/2014
15 11/11/2013 01/28/2014 10/01/2013 09/30/2017
;
proc print; run;

	PostProg_SD	PostProg_ED	Prog_SD	Prog_ED		postmon1	postmon2	postmon3	postmon4	postmon5	postmon6	postmon7	postmon8	postmon9	postmon10	postmon11	postmon12
11	03/02/2016	03/02/2017	05/29/2014	04/30/2017	365
12	04/21/2014	04/17/2015	09/23/2013	08/31/2014	361							2	30	30	30	30	30
13	01/01/2015	02/28/2015	10/01/2014	09/30/2015	58				31	28
14	03/24/2014	03/28/2014	01/13/2014	10/10/2014	4			4
15	11/11/2013	01/28/2014	10/01/2013	09/30/2017	78		20	30	30	30	30	30	30	30	30	30	30

[ChrisNZ]

You don't explain how the numbers are derived.

For example explain how you obtain the values on row #3 (5 30 30 30 30 7).

[JohnHoughton · Accepted solution]

Hi

I've worked out date ranges for 12 rolling months before and after the Prog_SD date, and then worked out number of days these overlap with the preprog_sd to preprog_ed date range. 

As it stands the number of days is inclusive of both the start and end date, but you may want to tune that. 

And obviously I haven't transposed the output.

The formula to work out the number of days overlap between two date ranges uses 

MAX(MIN(EndDate1,EndDate2)-MAX(StartDate1,StartDate2)+1,0)

which I found from https://wmfexcel.com/2014/10/25/how-to-calculate-number-of-overlapping-days-for-two-periods/

data want;
set have;
format  prefrom preto postfrom postto yymmdd10.;
LOS_Period=PreProg_ED-PreProg_SD;
do n=1 to 12;
prefrom=intnx('month',Prog_SD,-n,'sameday')+1;
preto=intnx('month',Prog_SD,-n+1,'sameday');
postfrom=intnx('month',Prog_SD,n-1,'sameday');
postto=intnx('month',Prog_SD,n,'sameday')-1;
predays=MAX(MIN(PreProg_ED,preto)-MAX(PreProg_SD,prefrom)+1,0);
postdays=MAX(MIN(PreProg_ED,postto)-MAX(PreProg_SD,postfrom)+1,0);
output;
end;
run;

[Solph]

Thanks so much. It worked. I just need to fix minor issues: 1) for cases with activity end date = program start date, reallocate the 1 day that falls under post-program period to the pre-program period, and 2) since it counts both the start and end dates it over-counts by 1 day and I need to tune it as you pointed out. And obviously transpose the data.

For people who are interested, here is the revised code combing pre- and post-cases. I also renamed the variable name (pre/post... to activity_SD and activity_ED, as activity period before and after the intervention).

data have; format id f2.0 Activity_SD Activity_ED Prog_SD Prog_ED yymmdd10.;
input @1 ID	@4 Activity_SD MMDDYY10. @15 Activity_ED MMDDYY10. @26 Prog_SD MMDDYY10. @37 Prog_ED MMDDYY10.; 
datalines;
1  01/18/2014 12/18/2014 12/18/2014 01/04/2017
2  03/19/2013 02/05/2014 02/18/2014 12/17/2014
3  01/01/2014 05/13/2014 09/08/2014 10/08/2015
4  01/26/2016 02/03/2016 03/03/2016 09/20/2016
5  01/11/2016 01/19/2016 01/19/2016 12/31/2016
6  08/10/2014 08/14/2014 08/14/2014 07/31/2017
11 03/02/2016 03/02/2017 05/29/2014 04/30/2017
12 04/21/2014 04/17/2015 09/23/2013 08/31/2014
13 01/01/2015 02/28/2015 10/01/2014 09/30/2015
14 03/24/2014 03/28/2014 01/13/2014 10/10/2014
15 11/11/2013 01/28/2014 10/01/2013 09/30/2017
;

data want; set have;
	format  prefrom preto postfrom postto yymmdd10.;
	LOS_Period=Activity_ED-Activity_SD;
	do n=1 to 12;
	prefrom=intnx('month',Prog_SD,-n,'sameday')+1;
	preto=intnx('month',Prog_SD,-n+1,'sameday');
	postfrom=intnx('month',Prog_SD,n-1,'sameday');
	postto=intnx('month',Prog_SD,n,'sameday')-1;
	predays=MAX(MIN(Activity_ED,preto)-MAX(Activity_SD,prefrom)+1,0);
	postdays=MAX(MIN(Activity_ED,postto)-MAX(Activity_SD,postfrom)+1,0);
	output;
	end;
run;