topic Re: Running total by multiple groups sorted by other column in SAS Programming

Running total by multiple groups sorted by other column

shparber_m — Wed, 21 Jul 2021 13:45:59 GMT

Hello friends,

I have the following table sorted by Customer_ID and Transaction_Date:

Customer_ID, Transaction_Date, Store_Region, Amount

I would like to calculate running total such that each time a Customer or a Store_Region change, it starts over again.

Tried several solutions but all require sorting by Store_Region - this is not my case.

It is important that the data stays sorted by Customer_ID and Transation_Date.

Thank you

Re: Running total by multiple groups sorted by other column

Kurt_Bremser — Wed, 21 Jul 2021 13:48:59 GMT

Sort by customer_id, store_region and transaction_date into a temporary dataset; calculate the running totals; then sort the result by customer_id and transaction_date.

Re: Running total by multiple groups sorted by other column

ballardw — Wed, 21 Jul 2021 14:13:25 GMT

It may help to provide an example of what you have with several different customer_id, transaction_date, store_region and amount. Then show what you expect the result to be for that small example (small enough to do by hand).

Re: Running total by multiple groups sorted by other column

shparber_m — Wed, 21 Jul 2021 14:19:27 GMT

But I do not want to sort by Store_Region!

Could be a situation where a customer buys in NJ then in NY and the in NJ again.

I need each purchase to reset my running total in this case.

If I order by Store_Region as you propose, then I'll have NJ, NJ, NY and it will add two NJ into the second running total.

I dont want that. I want to order only by Customer_ID and Transaction_Date.

Please help.

Thank you!

Re: Running total by multiple groups sorted by other column

FreelanceReinh — Wed, 21 Jul 2021 14:19:52 GMT

Hello @shparber_m and welcome to the SAS Support Communities!

Have you tried using the LAG function?

data want;
set have;
by Customer_ID Transaction_Date;
if first.Customer_ID or Store_Region ne lag(Store_Region) then rtotal=Amount;
else rtotal+Amount;
run;

Or, alternatively, the NOTSORTED option of the BY statement?

data want;
set have;
by Customer_ID Store_Region notsorted;
if first.Store_Region then rtotal=Amount;
else rtotal+Amount;
run;

Re: Running total by multiple groups sorted by other column

shparber_m — Wed, 21 Jul 2021 14:28:57 GMT

Sure! Here is a sample:
Each time that either customer_id or store_region change - I need to reset the running total.

The sort has to remain by Customer_ID and Transaction_Date only.

Please help.

Thank you!

Customer_ID	Transaction_Date	Store_Region	Amount	Running_Total
A	2021-07-01	NJ	100	100
A	2021-07-02	NJ	100	200
A	2021-07-03	TX	100	100
A	2021-07-04	NJ	100	100
A	2021-07-05	TX	100	100
A	2021-07-06	TX	100	200
B	2021-07-01	TX	10	10
B	2021-07-02	TX	10	20
B	2021-07-03	NJ	10	10
B	2021-07-04	TX	10	10
B	2021-07-05	NJ	10	10
B	2021-07-06	NJ	10	20

Re: Running total by multiple groups sorted by other column

shparber_m — Wed, 21 Jul 2021 14:36:17 GMT

That's awesome!

Thanks a lot @FreelanceReinh

Both your solutions work.

Thanks a lot!