## DATA Step, Macro, Functions and more

Frequent Contributor
Posts: 96

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
Super Contributor
Posts: 334

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

Regular Contributor
Posts: 195

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

Regular Contributor
Posts: 151

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;

Discussion stats
• 3 replies
• 260 views
• 0 likes
• 4 in conversation