topic Re: Please Help:How to order products in a dataset? Thanks in SAS Programming

Please Help:How to order products in a dataset? Thanks

Question — Mon, 20 May 2013 13:29:19 GMT

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

Re: Please Help:How to order products in a dataset? Thanks

esjackso — Mon, 20 May 2013 13:38:09 GMT

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.

Re: Please Help:How to order products in a dataset? Thanks

UrvishShah — Tue, 21 May 2013 07:15:51 GMT

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

Re: Please Help:How to order products in a dataset? Thanks

Keith — Tue, 21 May 2013 08:04:07 GMT

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;