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