How to do cycle grouping?

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How to do cycle grouping?

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 

Accepted Solutions
Solution
‎05-21-2018 03:39 PM
Super User
Posts: 10,778

Re: How to do cycle grouping?

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


All Replies
Regular Contributor
Posts: 215

Re: How to do cycle grouping?

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.

Super User
Posts: 23,724

Re: How to do cycle grouping?

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

 

 

PROC Star
Posts: 1,805

Re: How to do cycle grouping?

[ Edited ]

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;
Solution
‎05-21-2018 03:39 PM
Super User
Posts: 10,778

Re: How to do cycle grouping?

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;
New Contributor
Posts: 2

Re: How to do cycle grouping?

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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