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