topic Grouping Dates in Mathematical Optimization, Discrete-Event Simulation, and OR

Grouping Dates

Santha — Tue, 12 Jul 2022 09:13:16 GMT

I would like to do this in SAS. I will get two dates from the user. For example, the start date is 12/1/2022 and the end date is 12/21/2022 in this example below. After these two inputs, I also get a third input from the user, group#. If the group# is 1, then all dates the dates get a number starting from 1 to whatever is the last number applicable. If the group# is 2, then the first two dates (12/1 and 12/2) in this case below, gets a number of 1, 12/3 and 12/4 gets a number of 2. It is liking clubbing the dates into chunks that is determined by the group#. Group#3 would chunk the dates into set of 3. 12/1, 12/2 and 12/3 would get a value of 1. the next three dates would get a value of 2 so on and so forth.

Date	1_Group	2_Group	3_Group
12/1/2022	1	1	1
12/2/2022	2	1	1
12/3/2022	3	2	1
12/4/2022	4	2	2
12/5/2022	5	3	2
12/6/2022	6	3	2
12/7/2022	7	4	3
12/8/2022	8	4	3
12/9/2022	9	5	3
12/10/2022	10	5	4
12/11/2022	11	6	4
12/12/2022	12	6	4
12/13/2022	13	7	5
12/14/2022	14	7	5
12/15/2022	15	8	5
12/16/2022	16	8	6
12/17/2022	17	9	6
12/18/2022	18	9	6
12/19/2022	19	10	7
12/20/2022	20	10	7
12/21/2022	21	11	7

Re: Grouping Dates

PaigeMiller — Tue, 12 Jul 2022 11:34:34 GMT

data want;
    set have;
    group1+1;
    group2=1+floor((group1-1)/2);
    group3=1+floor((group1-1)/3);
run;

Re: Grouping Dates

RobPratt — Tue, 12 Jul 2022 19:16:19 GMT

Besides what @PaigeMiller proposed, here are two alternatives:

data want;
   set have;
   group1 + 1;
   group2 = ceil(group1/2);
   group3 = ceil(group1/3);
run;

data want;
   set have;
   group1 = _N_;
   group2 = ceil(_N_/2);
   group3 = ceil(_N_/3);
run;

Re: Grouping Dates

Santha — Thu, 14 Jul 2022 12:53:45 GMT

thank you Paige and Rob for your suggestions. I get your logic. Will try that out and I am confident that it will work.

Thanks both for your support

Re: Grouping Dates

Santha — Fri, 15 Jul 2022 04:55:52 GMT

Hi Paige and Rob

Sorry for the confusion. I did not clearly specify what I want . Here is my clarification. My inputs are

(a) lets say these three dates is what I have as my input. I want to chunk ify them. Pls see below.

Date

1/4/2022

1/7/2022

1/8/2022

(b) WindowNumber . Lets say Windownumber=2 (this means i want to chunk my date inputs into a set of 2 timechunks). what i want is a subset of this table below.

Date	Group2
1/4/2022	1
1/5/2022	1
1/6/2022	2
1/7/2022	2
1/8/2022	3
1/9/2022	3
1/10/2022	4
1/11/2022	4
1/12/2022	5

That is essentially this below. Essentially want the code to chunk-ify all the dates in between into Windownumbers and just give this small subset below.

Date	Group2
1/4/2022	1
1/7/2022	2
1/8/2022	3

Please let me know if this is not clear.

Re: Grouping Dates

RobPratt — Fri, 15 Jul 2022 19:29:05 GMT

data have;
   input Date mmddyy10.;
   datalines;
1/4/2022
1/7/2022
1/8/2022
;

data want(drop=first);
   set have;
   retain first;
   if _N_ = 1 then first = Date;
   Group2 = 1 + floor((Date-first)/2);
run;

proc print data=want;
   format Date mmddyy10.;
run;

Obs	Date	Group2
1	01/04/2022	1
2	01/07/2022	2
3	01/08/2022	3

Re: Grouping Dates

Santha — Sat, 16 Jul 2022 04:16:52 GMT

Rob. This is brilliant. Thank you for support as always