BookmarkSubscribeRSS Feed
Question
Fluorite | Level 6

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
3 REPLIES 3
esjackso
Quartz | Level 8

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

UrvishShah
Fluorite | Level 6

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

Keith
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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