Hi All,
I would like to order the product bought by each customer like below. The input is the original dataset and the output is the results I want. I want to follow the order of the product ...I woudl like the Milk for example to be first product bought for the first customer...
Your help will be much appreciated. Many Thanks
INPUT | OUTPUT | |||||
CustomerId | Delivery_date | Item_group | CustomerId | Item_group | Product_sequency | |
000001 | 01-Jan-11 | MILK | 000001 | MILK | 1 | |
000001 | 01-Jan-11 | JUICE | 000001 | JUICE | 2 | |
000001 | 02-Jan-11 | MILK | 000001 | CHEESE | 3 | |
000001 | 02-Jan-11 | JUICE | 000002 | MILK | 1 | |
000001 | 03-Jan-11 | CHEESE | 000002 | FRUITS | 2 | |
000002 | 01-Jan-11 | MILK | ||||
000002 | 02-Jan-11 | MILK | ||||
000002 | 03-Jan-11 | MILK | ||||
000002 | 04-Jan-11 | FRUITS |
Do you have the hierarchy in a table or defined somewhere? What happens if someone buys fruits and juice which is first? If all the items have a defined order say in a spreadsheet, you can read that into sas and merge the sequence into the input dataset and then sort the data by customer, date, and sequence.
EJ
Hi,
Try following code...
proc sort data = have;
by customer_id;
run;
data want;
set have;
by customer_id;
if first.customer_id then product_sequency = 1;
else product_sequency + 1;
run;
Hope it will be useful...
-Urvish
A 2 stage process will do it. Firstly group the customers and items, I've used PROC SUMMARY with the ORDER=DATA option to preserve the item order. Then you just need to add the sequence.
data have;
input CustomerId $ Delivery_date :date9. Item_group $;
datalines;
000001 01-Jan-11 MILK
000001 01-Jan-11 JUICE
000001 02-Jan-11 MILK
000001 02-Jan-11 JUICE
000001 03-Jan-11 CHEESE
000002 01-Jan-11 MILK
000002 02-Jan-11 MILK
000002 03-Jan-11 MILK
000002 04-Jan-11 FRUITS
;
run;
proc summary data=have nway;
class CustomerId Item_group / order=data;
output out=want (drop=_:);
run;
data want;
set want;
by customerid;
if first.customerid then product_sequency=0;
product_sequency+1;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.