BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zechliv
Calcite | Level 5

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.

 

ItemDay_of_WeekDelivery_DateDateDemandCycle_Demand
xds1MondayMonday5/14/20181222
xds1Tuesday 5/15/201810 
xds1WednesdayWednesday5/16/20181414
xds1Thursday 5/17/20180 
xds1FridayFriday5/18/20181533
xds1Saturday 5/19/20189 
xds1Sunday 5/20/20189 
xds1MondayMon5/21/2018816
xds1Tuesday 5/22/20188 
xds1WednesdayWed5/23/2018714
xds1Thursday 5/24/20187 
xds1FridayFri5/25/2018738
xds1Saturday 5/26/20186 
xds1Sunday 5/27/201812 
xds1Monday 5/28/201813 
ses2TuesdayTue5/15/20181024
ses2Wednesday 5/16/201814 
ses2ThursdayThu5/17/2018041
ses2Friday 5/18/201815 
ses2Saturday 5/19/20189 
ses2Sunday 5/20/20189 
ses2Monday 5/21/20188 
ses2TuesdayTue5/22/2018815
ses2Wednesday 5/23/20187 
ses2ThursdayThu5/24/2018744
ses2Friday 5/25/20187 
ses2Saturday 5/26/20186 
ses2Sunday 5/27/201815 
ses2Monday 5/28/20189 
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

5 REPLIES 5
tomrvincent
Rhodochrosite | Level 12

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.

Reeza
Super User

@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:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

 

novinosrin
Tourmaline | Level 20

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;
Ksharp
Super User
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;
zechliv
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 571 views
  • 4 likes
  • 5 in conversation