Allocating days across time periods

[TRJ]

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.

[Reeza]

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;

[PGStats]

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;

PG

[Ksharp]

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;