topic Allocating days across time periods in ODS and Base Reporting

Allocating days across time periods

TRJ — Fri, 07 Aug 2015 22:06:13 GMT

I've got a coding challenge and don't know how to start or even explain it well.

I'd like allocate the number of days over time/reporting periods. For example, if I wanted to figure out how many days cars were rented for by time period with only having the rental date, the car rental return date and the number of days the car was rented.

Time period data set/table

Period Start Date End Date

Period 01 Jan 01/15 Jan 28/15

Period 02 Jan 29/15 Feb 26/15

Period 03 Feb 27/15 Mar 25/15

Car Rental Date Returned Date Days rented

Car A Jan 15/15 Mar 1/15 46

Car B Feb 20/15 Mar 15/15 24

Would like to get results like these (I just figured out the number of days using a calendar so if I got them wrong, my apologies):

Period 01 Period 02 Period 03

Car A 14 29 3

Car B 7 17

Rental Days 14 36 20

Easy to transpose so:

Car A Period 01 28

Car A Period 02 29

Car A Period 03 3

Car B Period 02 7

Car B Period 03 17

Is cool also . . . Seems like this may be a DO LOOP, but do I start with rental date and move forward, or return date and work backwards? Like I said, if someone could please help me get started, I would very much appreciate it? Thank you.

Re: Allocating days across time periods

Reeza — Fri, 07 Aug 2015 22:09:25 GMT

1. Create a format from your time period rental table (period_fmt.)

2. Expand your Rental table so you have a record for every day

data expand_day;

set rental_table;

do day=rental_date to returned_date;

output;

end;

keep day car;

format day period_fmt.;

run;

3. Run a proc freq

proc freq data=expand_day;

table car*period/list out=want;

run;

Re: Allocating days across time periods

PGStats — Sat, 08 Aug 2015 01:13:42 GMT

Do this properly with SQL and INTCK :

proc sql;

create table periodRentals as

select

Car,

Period,

sum(intck("DAY", max(StartDate, RentalDate), min(EndDate, ReturnedDate))+1) as totalRentalDays

from periods inner join rentals

on StartDate<=ReturnedDate and EndDate>=RentalDate

group by Car, Period;

quit;

proc transpose data=periodRentals out=rentalTable(drop=_:);

by car;

var totalRentalDays;

id Period;

run;

Re: Allocating days across time periods

Ksharp — Sat, 08 Aug 2015 05:48:17 GMT

Code: Program1.sas

data Time;
input Period : $20.   StartDate : date9.   EndDate : date9.;
format StartDate  EndDate  date9.;
cards;
Period01 01Jan15 28Jan15
Period02 29Jan15 26Feb15
Period03 27Feb15 25Mar15
;
run;
data Car;
input car $   RentalDate   : date9.   ReturnedDate  : date9.   ;
format  RentalDate  ReturnedDate   date9.;
cards;
CarA 15Jan15 1Mar15  
CarB 20Feb15 15Mar15  
;
run;

data key;
 set time;
 do date=StartDate  to EndDate ;
  output;
 end;
 format date date9.;
 keep Period  date;
run;
data temp;
 set car;
 do date=RentalDate   to ReturnedDate  ;
  output;
 end;
 format date date9.;
 keep car date;
run;
proc sql;
create table want as 
 select car,Period,count(*) as n
  from key as a,temp as b 
   where a.date=b.date
   group by car,Period ;
quit;