Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Help on computing daily EOD Stock balance using either Retain or LAG f...

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-26-2023 07:22 AM
(300 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--------------------------

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 -

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

________________________

- Cheers -

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--------------------------

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

--------------------------

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.