I am trying to write a code that would calculate the Cycle_Demand (Sum of demand for each item_id between the delivery_day and the day before the next delivery_day). Can you please provide any ideas on how to address in SAS? Thank you for your expertise and insight.
Item | Day_of_Week | Delivery_Date | Date | Demand | Cycle_Demand |
xds1 | Monday | Monday | 5/14/2018 | 12 | 22 |
xds1 | Tuesday | 5/15/2018 | 10 | ||
xds1 | Wednesday | Wednesday | 5/16/2018 | 14 | 14 |
xds1 | Thursday | 5/17/2018 | 0 | ||
xds1 | Friday | Friday | 5/18/2018 | 15 | 33 |
xds1 | Saturday | 5/19/2018 | 9 | ||
xds1 | Sunday | 5/20/2018 | 9 | ||
xds1 | Monday | Mon | 5/21/2018 | 8 | 16 |
xds1 | Tuesday | 5/22/2018 | 8 | ||
xds1 | Wednesday | Wed | 5/23/2018 | 7 | 14 |
xds1 | Thursday | 5/24/2018 | 7 | ||
xds1 | Friday | Fri | 5/25/2018 | 7 | 38 |
xds1 | Saturday | 5/26/2018 | 6 | ||
xds1 | Sunday | 5/27/2018 | 12 | ||
xds1 | Monday | 5/28/2018 | 13 | ||
ses2 | Tuesday | Tue | 5/15/2018 | 10 | 24 |
ses2 | Wednesday | 5/16/2018 | 14 | ||
ses2 | Thursday | Thu | 5/17/2018 | 0 | 41 |
ses2 | Friday | 5/18/2018 | 15 | ||
ses2 | Saturday | 5/19/2018 | 9 | ||
ses2 | Sunday | 5/20/2018 | 9 | ||
ses2 | Monday | 5/21/2018 | 8 | ||
ses2 | Tuesday | Tue | 5/22/2018 | 8 | 15 |
ses2 | Wednesday | 5/23/2018 | 7 | ||
ses2 | Thursday | Thu | 5/24/2018 | 7 | 44 |
ses2 | Friday | 5/25/2018 | 7 | ||
ses2 | Saturday | 5/26/2018 | 6 | ||
ses2 | Sunday | 5/27/2018 | 15 | ||
ses2 | Monday | 5/28/2018 | 9 |
data have;
infile cards expandtabs;
input (Item Day_of_Week Delivery_Date date) (: $20.) Demand;
cards;
xds1 Monday Monday 5/14/2018 12 22
xds1 Tuesday . 5/15/2018 10
xds1 Wednesday Wednesday 5/16/2018 14 14
xds1 Thursday . 5/17/2018 0
xds1 Friday Friday 5/18/2018 15 33
xds1 Saturday . 5/19/2018 9
;
run;
data temp;
set have;
by item;
if first.item or not missing (Delivery_Date) then group+1;
run;
data want;
sum=0;
do until(last.group);
set temp;
by group;
sum+Demand;
end;
do until(last.group);
set temp;
by group;
if first.group then Cycle_Demand=sum;
else Cycle_Demand=.;
output;
end;
drop sum group;
run;
For each delivery date, find the next highest date and then find all records from the first date but less than the next date. Sum the amounts.
@zechliv if you can provide sample data as a data step someone can help with the code.
Here are the instructions on providing data as a data step:
Fairly straight forward if you understand the data:
/*set up grouping variable temp*/
data temp;
set have;
by item notsorted;
retain temp;
if first.item then call missing(temp);
if not missing(delivery_date) then temp=date;
format temp date9.;
run;
/*sum group by item, temp*/
proc sql;
create table want(drop=temp) as
select *,(date=min(date))*sum(demand) as cycle_demand
from temp
group by item, temp
order by item,date;
quit;
data have;
infile cards expandtabs;
input (Item Day_of_Week Delivery_Date date) (: $20.) Demand;
cards;
xds1 Monday Monday 5/14/2018 12 22
xds1 Tuesday . 5/15/2018 10
xds1 Wednesday Wednesday 5/16/2018 14 14
xds1 Thursday . 5/17/2018 0
xds1 Friday Friday 5/18/2018 15 33
xds1 Saturday . 5/19/2018 9
;
run;
data temp;
set have;
by item;
if first.item or not missing (Delivery_Date) then group+1;
run;
data want;
sum=0;
do until(last.group);
set temp;
by group;
sum+Demand;
end;
do until(last.group);
set temp;
by group;
if first.group then Cycle_Demand=sum;
else Cycle_Demand=.;
output;
end;
drop sum group;
run;
I appreciate your taking the time to help me with getting the data into a dataset and providing a solution code. I am actually adapting your code the to the rest of my population.
Thanks!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: