Calcite | Level 5

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

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.

Regards,

Ananda

1 ACCEPTED SOLUTION

Accepted Solutions

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

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
6 REPLIES 6
Barite | Level 11

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

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?

________________________

- Cheers -

Calcite | Level 5

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

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

Calcite | Level 5

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

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
Barite | Level 11

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

Hi,

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.

________________________

- Cheers -

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

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Calcite | Level 5

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

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
Discussion stats
• 6 replies
• 301 views
• 0 likes
• 3 in conversation