Creating events with dates

newsas007 — Tue, 07 Jul 2020 17:37:57 GMT

Hi all,

Here is my dataset

ID	Product_Order	Product_Received
1	10/6/2018	10/23/2018
1	10/6/2018	10/31/2018
1	10/6/2018	12/3/2018
2	10/13/2018	11/1/2018
2	10/13/2018	12/18/2018
3	7/16/2016	7/26/2016
3	10/2/2017	10/22/2017
4	12/10/2016	12/27/2016
4	2/13/2018	3/5/2018
5	1/24/2017	2/12/2017
5	12/26/2017	1/12/2018
5	1/10/2018	2/20/2018
6	2/6/2018	3/10/2018
6	2/9/2018	3/10/2018
7	4/5/2018	5/5/2018
7	5/5/2018	6/4/2018
7	6/5/2018	7/5/2018
7	7/5/2018	8/4/2018

I would like to calculate the order event from these dates. See below:

Here are the rules:

If the product_order date is the same date for ID then the order_event is the same. For example, ID#1 has the same order dates so the order event is still 1.

Similary, if the product was received on the same day the order_even is the same. For example, ID#6 has the same product_received day so the order_event is still 1.

Notice for #3: the second and third order is the same date so the order number is 2. This will be the same for same product received dates

For the rest, different order and received dates are added as a count.

This is the final data:

ID	Product_Order	Product_Received	Order_event
1	10/6/2018	10/23/2018	1
1	10/6/2018	10/31/2018	1
1	10/6/2018	12/3/2018	1
2	10/13/2018	11/1/2018	1
2	10/13/2018	12/18/2018	1
3	7/16/2016	7/26/2016	1
3	10/2/2017	10/22/2017	2
3	10/2/2017	10/14/2017	2
4	12/10/2016	12/27/2016	1
4	2/13/2018	3/5/2018	2
5	1/24/2017	2/12/2017	1
5	12/26/2017	1/12/2018	2
5	1/10/2018	2/20/2018	3
6	2/6/2018	3/10/2018	1
6	2/9/2018	3/10/2018	1
7	4/5/2018	5/5/2018	1
7	5/5/2018	6/4/2018	2
7	6/5/2018	7/5/2018	3
7	7/5/2018	8/4/2018	4

Re: Creating events with dates

smantha — Tue, 07 Jul 2020 18:04:13 GMT

data have;
informat id 3. Product_Order mmddyy10. Product_Received mmddyy10.;
input id Product_Order Product_Received;
format id 3. Product_Order mmddyy10. Product_Received mmddyy10.;
cards;
1	10/6/2018	10/23/2018
1	10/6/2018	10/31/2018
1	10/6/2018	12/3/2018
2	10/13/2018	11/1/2018
2	10/13/2018	12/18/2018
3	7/16/2016	7/26/2016
3	10/2/2017	10/22/2017
4	12/10/2016	12/27/2016
4	2/13/2018	3/5/2018
5	1/24/2017	2/12/2017
5	12/26/2017	1/12/2018
5	1/10/2018	2/20/2018
6	2/6/2018	3/10/2018
6	2/9/2018	3/10/2018
7	4/5/2018	5/5/2018
7	5/5/2018	6/4/2018
7	6/5/2018	7/5/2018
7	7/5/2018	8/4/2018
;;;
run;
proc sort; by id Product_Order Product_Received;
run;
data want;
set have;
by id Product_Order Product_Received;
retain order_event prev_order prev_recv;
if first.id then do;
 order_event=1;
 prev_order=Product_Order;
 prev_recv=Product_Received;
end;
put _all_;
if (prev_order ^= Product_Order) and (prev_recv^=Product_Received) then order_event=order_event+1;
prev_order=Product_Order;
prev_recv=Product_Received;
run;

topic Re: Creating events with dates in SAS Programming

Creating events with dates

Re: Creating events with dates