SAS First in first out - Financial calculation

Ranny — Tue, 09 Apr 2019 17:36:32 GMT

Problem:- I want to create First in First Out (FIFO) calculation in attached spreadsheet. Means All points Earned Under (Earned and Adjusted) column throughout period will be my Starting balance.

Now Anytime when Account has either Purge or Expire or Redeem points in future period It should deduct from prior Points earned under Earned and Adjusted variable.

For Example, Total earned points at the end of period 200812 will be 15,000 (12,00 from Earn + 3000 from Adjusted column) And 10,250 Debited points (1,500 from Purge, 500 from Expire and 8250 from Redeem column). Means Account has 4,750 points left. (15,000 – 10,250= 4,750).

However, I need 10,250 points distributed from lower to higher period using FIFO. Means 1,500 points from Purge column should be deducted from the balance of 2,000 points (Earned +Adjusted column) for period 200801 and 200802. After 1500 points deduction we still have balance of 500 which will be used to deduct 500 Expire points. Now we don’t have any balance left for period 200802. So for redeemed points of 8250 should start deducting from period 200803 onwards. It should deduct the points from earned bucket (Earned +Adjusted) until became zero.

Please refer attached file for soft copy. Ultimately My desire output is in green on the right side.

I tried to explain in detail as much as possible but please feel free to revert back if you need any more additional information.

The attached excel has the example by every month and how the data is going layout month by month. I have tried to explain 12month scenario, but it can go all the way up to 150 month for one account.

Thank you

Re: SAS First in first out - Financial calculation

Ksharp — Wed, 10 Apr 2019 13:24:45 GMT

Here is some dummy code.

data _null_;
 set sashelp.class;
 array x{4} _temporary_ ;
 do i=3 to 1 by -1;
  x{i+1}=x{i};
 end;
 x{1}=age;
 put 'FIFO:age=' x{4};
run;

topic SAS First in first out - Financial calculation in SAS Programming

SAS First in first out - Financial calculation

Re: SAS First in first out - Financial calculation