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