DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 96

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

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
CustomerIdDelivery_dateItem_group CustomerIdItem_groupProduct_sequency
00000101-Jan-11MILK 000001MILK1
00000101-Jan-11JUICE 000001JUICE2
00000102-Jan-11MILK 000001CHEESE3
00000102-Jan-11JUICE 000002MILK1
00000103-Jan-11CHEESE 000002FRUITS2
00000201-Jan-11MILK
00000202-Jan-11MILK
00000203-Jan-11MILK
00000204-Jan-11FRUITS
Super Contributor
Posts: 333

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

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

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

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

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

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=_Smiley Happy;

run;

data want;

set want;

by customerid;

if first.customerid then product_sequency=0;

product_sequency+1;

run;

Ask a Question
Discussion stats
  • 3 replies
  • 196 views
  • 0 likes
  • 4 in conversation