Help on computing daily EOD Stock balance using either Retain or LAG function

[sinako]

Hi Experts:

     Am trying to compute daily stock holdings based on the 'Arrival' and 'Departs' of different product codes. I have only the EOD Balance of holdings of each stock. As I need to identify HOW  much qty has come in and went out, on every day and what would be the balance held on each day. This is very much required to compute the rental charges on those quantities held on such dates.

I tried the below code and unable to achieve my expected results. Can anyone help me on this ?

Sample input data:

data MyStockTab;
infile datalines missover;
input DAYS Date9. ProductCode $8. TYPE $2. Arrived Departed RealBalance ;
format DAYS DAte9.;
informat DAYS DAte9.;
cards;
26JUL2021 MB6448 A 21 0 220
25JUL2021 MB6448 A 14 0 0
24JUL2021 MB6448 A 28 0 0
23JUL2021 MB6448 A 31 0 0
22JUL2021 MB6448 A 20 0 0
21JUL2021 MB6448 D 0 30 0
21JUL2021 MB6448 A 11 0 0
20JUL2021 MB6448 A 41 0 0
25JUN2021 MB3748 A 64 0 320
20JUN2021 MB3748 D 0 18 0
18JUN2021 MB3748 A 39 0 0
12JUN2021 MB3748 A 25 0 0
11JUN2021 MB3748 D 0 30 0
;

RUN;

*Sorting the descending order days, Asceding on TYPE;

proc sort data=MyStockTab out=MyStockSort;
by ProductCode days descending TYPE ;
run;

*Trying to find out the daily stock balance;

data MyStockTable ;
set MyStockSort;
by ProductCode ;
retain Arrived Departed DailyBalance;
if first.ProductCode then DailyBalance=RealBalance;
DailyBalance = DailyBalance + Lag(Departed) - Lag(Arrived) ;
run;

Attached is the XLS file which contains both data and the expected column output.
In Excel sheet, I wrote the formulae using cell reference but am trying to perform same via SAS.

Please help in correcting my code,

Regards,

Ananda

[Oligolas]

Hi,

why do you omit to calculate the DailyBalance with 41 arrived on 20JUL2021 for MB6448 in your example sheet?

Same question for MB3748, you do not include the last line (Departed 30) in your calculation

and why do you add departed and substract arrived? Shouldn't it be the opposite?

[sinako]

Dear Sir/Madam:

Thank you for your reply and valid question.

I have two separate tables, one table contains only the Last Balance of each stock and another one which has only in-out info like How much arrived and how much departed. I merged both tables.

NOW , I need to compute the daily EOD balance of stock which is on hold with us. Holding of stocks incurs charges of refrigeration, electricity, logistics, rentals, transportation, etc on daily basis. So, the expenditure upon balance held on EOD basis should be calculated to minimize the overall cost.

Regarding your question, the last line whether it could be depart or arrived stock, but that should be taken into consideration so to define the DailyBalance value.

To get better understanding, you can re-sort my data in ascending order of the DAYS, where you can see that, any in-out values are stored in respective columns but the DailyBalance value is computed as final value i.e.available balance for the day (by adding or substracting respective movements of stocks).

Hope I could be able to clarify.

Regards,

Ananda

[sinako]

Hi Expert:

Just to add a point. As I do not have any starting point of the Balance value. I am doing a retro-calculation from the current balance backwards to compute the daily EOD balance of stock. Below is example output for your reference.
DAYS P_Code TYPE Arv Dpt RBal My Exp. Balance
26-Jul-21 MB6448 A 21 0 223 223
25-Jul-21 MB6448 A 14 0 0 202
24-Jul-21 MB6448 A 28 0 0 188
23-Jul-21 MB6448 A 31 0 0 160
22-Jul-21 MB6448 A 20 0 0 129
21-Jul-21 MB6448 D 0 30 0 109
21-Jul-21 MB6448 A 11 0 0 139
20-Jul-21 MB6448 A 41 0 0 128
25-Jun-21 NB3748 A 64 0 320 320
20-Jun-21 NB3748 D 0 18 0 256
18-Jun-21 NB3748 A 39 0 0 274
12-Jun-21 NB3748 A 25 0 0 235
11-Jun-21 NB3748 D 0 30 0 210

Hope it helps,

Regards,
Ananda

[Oligolas]

Hi,

please re-read my question and answer correspondingly.

In my opinion your calculation is both wrong in the result and the method.

IMHO the line 

20-Jul-21 MB6448 A 41 0 0 128

should be 

20-Jul-21 MB6448 A 41 0 0 128-41=87!

AND

in your code the formula "DailyBalance = DailyBalance + Lag(Departed) - Lag(Arrived) ;"

should be

DailyBalance = DailyBalance - Lag(Departed) + Lag(Arrived) ;

so that the whole results are wrong.

Please confirm.

[mkeintz · Accepted solution]

You have a current balance, and a history of arrivals and departures.  You want to reconstruct the historic balances.  I.e. today's arrival should be subtracted from today's balance to generate yesterday's balance.

So you sorted data by descending date within productcode.  Then: 

data want (drop=_:);
  set mystocktab ;
  by descending productcode descending days;

  _nxt_arrived=lag(arrived);
  _nxt_departed=lag(departed);

  retain expected_balance;
  if first.productcode then expected_balance=realbalance;
  else expected_balance=expected_balance-_nxt_arrived+_nxt_departed;
  
run;

Even though I use the lag function (which usually implies retrieval of a chronologically preceding value), I name the resulting variables with a prefix of _NXT_, because data are proceeding in reverse chronological order.

[sinako]

Hi Expert ( mkeintz):

Thank you very much for your solution. It worked well !!!..

My sincere thanks and very heartful gratitude for your trick.

Very nice you,

Regards,

Ananda