How to do cycle grouping?

[zechliv]

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

[tomrvincent]

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]

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

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 · Accepted solution]

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]

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!