How to Add Extra Rows between dates and perform prorated calculation (with SAS EG 7.1)

Anav0416 — Tue, 09 Mar 2021 03:20:29 GMT

Hi,

I have the following sample tables with time cycle starts from midnight (0:00:00) to 23:59:59. I need to perform the following tasks:

1. If the duration in hours (duration_hr) > hours to midnight (hr_to_midnight) but span over only to the next day (refer to row 6 in HAVE table) then do

set row 6 duration_hr=hr_to_midnight
add extra row between row 6 and 7 then (1) advance date by 1 day, (2) set time to 0:00:00, (3) carry forward the previous rate to the new rate variable, (4) set the remaining duration as the new value for the duration_hr variable (refer to row 8 in WANT table)

2. If the duration in hours (duration_hr) > hours to midnight (hr_to_midnight) but span over multiple days (refer to row 7-8 in HAVE table) then perform similar tasks describe in #1 and add additional rows until all durations in hours are accounted for (i.e., produce rows 10-15 in the WANT table for the entry on 5/2/17 in the HAVE table, and rows 17-20 in the WANT table for the entry on 5/8/17 in the HAVE table)

3. If it is the last record for a subject and the duration in hours (duration_hr) > hours to midnight (hr_to_midnight) then just set the duration_hr=hr_to_midnight.

Working Syntax Codes:

data want;
set have;
by subjectID;

if last.subjectID and dur_hr>hrToMidnight then dur_hr=hrToMidnight;

retain rDur 0;
if dur_hr>hrToMidnight then do;
rDur=dur_hr-hrToMidnight;
dur_hr=hrToMidnight;
/* do; (perform a do while loop here?)
dur_hr=rDur;
date=preDt+1;
time=.;
output;
end;*/
end;
run;

Q: I am able to reset the values but not able to produce the extra rows correctly at the moment. I am working on the program but any help is greatly appreciated.

Thank you.

Have
Obs	subjectID	date	time	delivery_type	rate	duration_hr	previous_date	hr_to_midnight
1	1	5/1/2017	5:00:00	scheduled	0.5	0.816388889	30-Apr-17	19
2	1	5/1/2017	5:48:59	temp	0	1.248333333	1-May-17	18.18361111
3	1	5/1/2017	7:03:53	temp	0	0.748888889	1-May-17	16.93527778
4	1	5/1/2017	7:48:49	scheduled	0.75	2.253333333	1-May-17	16.18638889
5	1	5/1/2017	10:04:01	scheduled	0.8	0.500833333	1-May-17	13.93305556
6	1	5/1/2017	10:34:04	scheduled	0	36.36111111	1-May-17	13.43222222
7	1	5/2/2017	23:00:34	scheduled	0	133.0694444	1-May-17	0.990555556
8	1	5/8/2017	22:08:12	scheduled	0	96.00222222	2-May-17	1.863333333
9	1	5/12/2017	22:15:55	scheduled	0	0.581666667	8-May-17	1.734722222

Want
1	subjectID	date	time	delivery type	rate	duration_hr	previoud_date	hr_to_midnight
2	1	5/1/2017	5:00:00	scheduled	0.5	0.816388889	30-Apr-17	19
3	1	5/1/2017	5:48:59	temp	0	1.248333333	1-May-17	18.18361111
4	1	5/1/2017	7:03:53	temp	0	0.748888889	1-May-17	16.93527778
5	1	5/1/2017	7:48:49	scheduled	0.75	2.253333333	1-May-17	16.18638889
6	1	5/1/2017	10:04:01	scheduled	0.8	0.500833333	1-May-17	13.93305556
7	1	5/1/2017	10:34:04	scheduled	0	13.43222222	1-May-17	13.43222222
8	1	5/2/2017	0:00:00	scheduled	0	22.92888889	1-May-17	24
9	1	5/2/2017	23:00:34	scheduled	0	0.990555556	1-May-17	0.990555556
10	1	5/3/2017	0:00:00	scheduled	0	24	2-May-17	24
11	1	5/4/2017	0:00:00	scheduled	0	24	3-May-17	24
12	1	5/5/2017	0:00:00	scheduled	0	24	4-May-17	24
13	1	5/6/2017	0:00:00	scheduled	0	24	5-May-17	24
14	1	5/7/2017	0:00:00	scheduled	0	24	6-May-17	24
15	1	5/8/2017	0:00:00	scheduled	0	12.07888884	7-May-17	24
16	1	5/8/2017	22:08:12	scheduled	0	1.863333333	7-May-17	1.863333333
17	1	5/9/2017	0:00:00	scheduled	0	24	8-May-17	24
18	1	5/10/2017	0:00:00	scheduled	0	24	9-May-17	24
19	1	5/11/2017	0:00:00	scheduled	0	24	10-May-17	24
20	1	5/12/2017	0:00:00	scheduled	0	22.13888887	11-May-17	24
21	1	5/12/2017	22:15:55	scheduled	0	0.581666667	11-May-17	1.734722222

Re: How to Add Extra Rows between dates and perform prorated calculation (with SAS EG 7.1)

andreas_lds — Tue, 09 Mar 2021 06:05:09 GMT

Please post the data in usable form and use "insert sas code" to post the code preserving the formatting and making code readable.

Why to i ask for usable data? From the table you have posted it is hardly possible to know if "date" and "time" are properly imported.

The whole task sound like homework.

Re: How to Add Extra Rows between dates and perform prorated calculation (with SAS EG 7.1)

Anav0416 — Tue, 09 Mar 2021 06:27:54 GMT

I figured it out.

Re: How to Add Extra Rows between dates and perform prorated calculation (with SAS EG 7.1)

Amir — Tue, 09 Mar 2021 07:23:28 GMT

Hi @Anav0416,

Well done on figuring it out. Sharing your solution will help others who have a similar problem.

Thanks & kind regards,

Amir.

topic How to Add Extra Rows between dates and perform prorated calculation (with SAS EG 7.1) in SAS Programming

How to Add Extra Rows between dates and perform prorated calculation (with SAS EG 7.1)

Re: How to Add Extra Rows between dates and perform prorated calculation (with SAS EG 7.1)

Re: How to Add Extra Rows between dates and perform prorated calculation (with SAS EG 7.1)

Re: How to Add Extra Rows between dates and perform prorated calculation (with SAS EG 7.1)