Desktop productivity for business analysts and programmers

Retaining Last Transaction byProduct Id

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Retaining Last Transaction byProduct Id

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


Accepted Solutions
Solution
‎10-28-2014 03:48 AM
Super Contributor
Posts: 336

Re: Retaining Last Transaction byProduct Id

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


All Replies
Trusted Advisor
Posts: 1,203

Re: Retaining Last Transaction byProduct Id

proc sql;

select * from have

group by prodcode

having OrderNumber=max(OrderNumber);

quit;

Occasional Contributor
Posts: 11

Re: Retaining Last Transaction byProduct Id

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
Trusted Advisor
Posts: 1,203

Re: Retaining Last Transaction byProduct Id

Hi,

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

Occasional Contributor
Posts: 11

Re: Retaining Last Transaction byProduct Id

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.

Trusted Advisor
Posts: 1,203

Re: Retaining Last Transaction byProduct Id

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

Occasional Contributor
Posts: 11

Re: Retaining Last Transaction byProduct Id

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
Occasional Contributor
Posts: 11

Re: Retaining Last Transaction byProduct Id

Thanks for the help stat@sas

Esteemed Advisor
Posts: 6,646

Re: Retaining Last Transaction byProduct Id

How about:

proc sort data=have;

by CustCode ProdCode descending ProdRunDateKey;

run;

data want;

set have;

by CustCode ProdCode;

if first.ProdCode;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎10-28-2014 03:48 AM
Super Contributor
Posts: 336

Re: Retaining Last Transaction byProduct Id

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;

Occasional Contributor
Posts: 11

Re: Retaining Last Transaction byProduct Id

Hi User24Feb

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 524 views
  • 3 likes
  • 4 in conversation