BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
manojdawson
Calcite | Level 5

Hi

I am trying to find when a Customer bought  a particular product last to estimate reorder days for each product. I want to retain only the latest purchase record for every product bought by CM's.

My current data looks like below.

ProdRunDateKeyCustCodeOrderNumberProdCodeLongDescription
2014071754015031646504211Ginkgo 2000
2014071754015031646504452Healthy Joints
2014041654015031143344211Ginkgo 2000
2014041654015031143344452Healthy Joints
2014081954025831829871513Super B 50
2014081954025831829871616Super C
2014081954025831829872123Zinc Plus
2014081954025831829872171Magnesium Plus
2014052254025831321821513Super B 50
2014052254025831321822123Zinc Plus
2014052254025831321823324Hair Skin & Nails Nutrition
2014052254025831321823533Women's Daily Vitality Multi

So  I would want the output to look like

ProdRunDateKeyCustCodeOrderNumberProdCodeLongDescription
2014071754015031646504211Ginkgo 2000
2014071754015031646504452Healthy Joints
2014081954025831829871513Super B 50
2014081954025831829871616Super C
2014081954025831829872123Zinc Plus
2014081954025831829872171Magnesium Plus
2014052254025831321823324Hair Skin & Nails Nutrition
2014052254025831321823533Women's Daily Vitality Multi

Could someone help me with this please?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
user24feb
Barite | Level 11

Could it be that you simply need to add "ProdCode" to stat@sas's group by statement?

Data Have;
  Input @1 ProdRunDateKey 8. @10 CustCode 6. @17 OrderNumber 7. @25 ProdCode 4. @30 LongDescription $50.;
  Datalines;
20140717 540150 3164650 4211 Ginkgo 2000
20140717 540150 3164650 4452 Healthy Joints
20140416 540150 3114334 4211 Ginkgo 2000
20140416 540150 3114334 4452 Healthy Joints
20140819 540258 3182987 1513 Super B 50
20140819 540258 3182987 1616 Super C
20140819 540258 3182987 2123 Zinc Plus
20140819 540258 3182987 2171 Magnesium Plus
20140522 540258 3132182 1513 Super B 50
20140522 540258 3132182 2123 Zinc Plus
20140522 540258 3132182 3324 Hair Skin & Nails Nutrition
20140522 540258 3132182 3533 Women's Daily Vitality Multi
;
Run;

Proc SQL NoPrint;
  Create Table Want As
  Select * From Have
  Group By CustCode, ProdCode
  Having OrderNumber = Max(OrderNumber);
Quit;

View solution in original post

10 REPLIES 10
stat_sas
Ammonite | Level 13

proc sql;

select * from have

group by prodcode

having OrderNumber=max(OrderNumber);

quit;

manojdawson
Calcite | Level 5

Hi

this does not give me the output I am looking for. Instead it gives me only the very last day in the file and the biggest order number on the day

Eg

ProdRunDateKeyCustCodeOrderNumberCodeLongDescription
2014102773157832201641082Krill Oil 500mg 30 caps
20141027998332201821133Super Strength Cod Liver Oil
2014102721145132205821152Natural Vitamin E 500IU
2014102727792032205931154Vitamin D3 1000IU
2014102767201632206431177Fish Oil 1000mg
201410275943832205711178Enteric Coated Fish Oil 1000mg
stat_sas
Ammonite | Level 13

Hi,

This is not the same output you showed in question. What is the difference between ProdCode and Code?

manojdawson
Calcite | Level 5

Its the same heading. I just renamed it.

The output I showed last is what I am getting if I use the code you suggested. I was just showing you as a sample of the output I am getting based on  the code.

The output I am after is still what I mentioned in my first question.

stat_sas
Ammonite | Level 13

How about if you apply suggested code on the data provided in the question? Are you getting the desired output?

manojdawson
Calcite | Level 5

It still only shows me the same output as shown. It shows products bought yesterday with the max order number (i.e. the very last order placed yesterday for every SKU(productCode))

It seems to be ignoring CustCode.

Maybe If I try and explain more clearly you will be able to help

So What I am trying to achieve is predict when someone is likely to run out of a particular product

ProdRunDateKeyCustCodeOrderNumberProdCodeLongDescription
2014071754015031646504211Ginkgo 2000
2014071754015031646504452Healthy Joints
2014041654015031143344211Ginkgo 2000
2014041654015031143344452Healthy Joints
2014081954025831829871513Super B 50
2014081954025831829871616Super C
2014081954025831829872123Zinc Plus
2014081954025831829872171Magnesium Plus
2014052254025831321821513Super B 50
2014052254025831321822123Zinc Plus
2014052254025831321823324Hair Skin & Nails Nutrition
2014052254025831321823533Women's Daily Vitality Multi

In the above data set, Customer - 540150 bought Gingko & Healthy Joints twice. But in the output I would like to see only the very last Gingko & Healthy Joints they bought (i.e. Order Number 3164650). So Order 3114334 should be ignored

Similarly for Customer - 540258, I would like to get Super B 50, Super C, Zinc, Magnesium, Hair Skin & Nails and Women's Daily in the output. So it ignores when the previous time the first 4 products were bought and only retains the very last time a SKU was bought , thereby taking 4 SKU's from the last order and 2 SKU's from  the previous order

So  I would want the output to look like

ProdRunDateKeyCustCodeOrderNumberProdCodeLongDescription
2014071754015031646504211Ginkgo 2000
2014071754015031646504452Healthy Joints
2014081954025831829871513Super B 50
2014081954025831829871616Super C
2014081954025831829872123Zinc Plus
2014081954025831829872171Magnesium Plus
2014052254025831321823324Hair Skin & Nails Nutrition
2014052254025831321823533Women's Daily Vitality Multi
manojdawson
Calcite | Level 5

Thanks for the help stat@sas

user24feb
Barite | Level 11

Could it be that you simply need to add "ProdCode" to stat@sas's group by statement?

Data Have;
  Input @1 ProdRunDateKey 8. @10 CustCode 6. @17 OrderNumber 7. @25 ProdCode 4. @30 LongDescription $50.;
  Datalines;
20140717 540150 3164650 4211 Ginkgo 2000
20140717 540150 3164650 4452 Healthy Joints
20140416 540150 3114334 4211 Ginkgo 2000
20140416 540150 3114334 4452 Healthy Joints
20140819 540258 3182987 1513 Super B 50
20140819 540258 3182987 1616 Super C
20140819 540258 3182987 2123 Zinc Plus
20140819 540258 3182987 2171 Magnesium Plus
20140522 540258 3132182 1513 Super B 50
20140522 540258 3132182 2123 Zinc Plus
20140522 540258 3132182 3324 Hair Skin & Nails Nutrition
20140522 540258 3132182 3533 Women's Daily Vitality Multi
;
Run;

Proc SQL NoPrint;
  Create Table Want As
  Select * From Have
  Group By CustCode, ProdCode
  Having OrderNumber = Max(OrderNumber);
Quit;

manojdawson
Calcite | Level 5

Hi User24Feb

Thank you for the help. Yes it was just a tweak to the code and it worked

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 10 replies
  • 1419 views
  • 3 likes
  • 4 in conversation