DATA Step, Macro, Functions and more

Assign values by rolling month

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 109
Accepted Solution

Assign values by rolling month

[ Edited ]

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

Accepted Solutions
Solution
‎11-08-2017 01:56 PM
Contributor
Posts: 62

Re: Assign values by rolling month

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;

 

 

View solution in original post


All Replies
PROC Star
Posts: 2,318

Re: Assign values by rolling month

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).

 

 

Solution
‎11-08-2017 01:56 PM
Contributor
Posts: 62

Re: Assign values by rolling month

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;

 

 

Frequent Contributor
Posts: 109

Re: Assign values by rolling month

Posted in reply to JohnHoughton

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 336 views
  • 0 likes
  • 3 in conversation