topic Re: Retaining Last Transaction byProduct Id in SAS Enterprise Guide

Retaining Last Transaction byProduct Id

manojdawson — Tue, 28 Oct 2014 00:48:57 GMT

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

Re: Retaining Last Transaction byProduct Id

stat_sas — Tue, 28 Oct 2014 01:22:37 GMT

proc sql;

select * from have

group by prodcode

having OrderNumber=max(OrderNumber);

quit;

Re: Retaining Last Transaction byProduct Id

manojdawson — Tue, 28 Oct 2014 04:20:24 GMT

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

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

Re: Retaining Last Transaction byProduct Id

stat_sas — Tue, 28 Oct 2014 04:33:43 GMT

Hi,

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

Re: Retaining Last Transaction byProduct Id

manojdawson — Tue, 28 Oct 2014 04:41:04 GMT

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.

Re: Retaining Last Transaction byProduct Id

stat_sas — Tue, 28 Oct 2014 05:06:27 GMT

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

Re: Retaining Last Transaction byProduct Id

manojdawson — Tue, 28 Oct 2014 05:22:29 GMT

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

Re: Retaining Last Transaction byProduct Id

Kurt_Bremser — Tue, 28 Oct 2014 07:18:46 GMT

How about:

proc sort data=have;

by CustCode ProdCode descending ProdRunDateKey;

run;

data want;

set have;

by CustCode ProdCode;

if first.ProdCode;

run;

Re: Retaining Last Transaction byProduct Id

user24feb — Tue, 28 Oct 2014 07:48:35 GMT

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;

Re: Retaining Last Transaction byProduct Id

manojdawson — Tue, 28 Oct 2014 22:30:18 GMT

Hi User24Feb

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

Re: Retaining Last Transaction byProduct Id

manojdawson — Tue, 28 Oct 2014 22:30:49 GMT

Thanks for the help stat@sas