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

[Anav0416]

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

[andreas_lds]

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.

[Anav0416]

I figured it out.

[Amir]

Hi @Anav0416,

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

Thanks & kind regards,

Amir.