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.
ProdRunDateKey | CustCode | OrderNumber | ProdCode | LongDescription |
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 |
So I would want the output to look like
ProdRunDateKey | CustCode | OrderNumber | ProdCode | LongDescription |
20140717 | 540150 | 3164650 | 4211 | Ginkgo 2000 |
20140717 | 540150 | 3164650 | 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 | 3324 | Hair Skin & Nails Nutrition |
20140522 | 540258 | 3132182 | 3533 | Women's Daily Vitality Multi |
Could someone help me with this please?
Thanks
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;
proc sql;
select * from have
group by prodcode
having OrderNumber=max(OrderNumber);
quit;
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
ProdRunDateKey | CustCode | OrderNumber | Code | LongDescription |
20141027 | 731578 | 3220164 | 1082 | Krill Oil 500mg 30 caps |
20141027 | 9983 | 3220182 | 1133 | Super Strength Cod Liver Oil |
20141027 | 211451 | 3220582 | 1152 | Natural Vitamin E 500IU |
20141027 | 277920 | 3220593 | 1154 | Vitamin D3 1000IU |
20141027 | 672016 | 3220643 | 1177 | Fish Oil 1000mg |
20141027 | 59438 | 3220571 | 1178 | Enteric Coated Fish Oil 1000mg |
Hi,
This is not the same output you showed in question. What is the difference between ProdCode and Code?
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.
How about if you apply suggested code on the data provided in the question? Are you getting the desired output?
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
ProdRunDateKey | CustCode | OrderNumber | ProdCode | LongDescription |
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 |
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
ProdRunDateKey | CustCode | OrderNumber | ProdCode | LongDescription |
20140717 | 540150 | 3164650 | 4211 | Ginkgo 2000 |
20140717 | 540150 | 3164650 | 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 | 3324 | Hair Skin & Nails Nutrition |
20140522 | 540258 | 3132182 | 3533 | Women's Daily Vitality Multi |
Thanks for the help stat@sas
How about:
proc sort data=have;
by CustCode ProdCode descending ProdRunDateKey;
run;
data want;
set have;
by CustCode ProdCode;
if first.ProdCode;
run;
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;
Hi User24Feb
Thank you for the help. Yes it was just a tweak to the code and it worked
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.