SAS First in First OUT (FIFO)

[Ricky3011]

Have	Want
								Desire Result After FIFO Effect
Account	Period	Earned	Adjusted	Purge	Expire	Redeem	Available	Purge	Expire	Redeem	Available
A	200801	1,000	-	-	-	-	1,000	1,000			-
A	200802	1,000	-	500	-	500	1,000	500	500		-
A	200803	1,000	500	-	-	-	2,500			1,500	-
A	200804	1,000	-	250	-	2,500	750			1,000	-
A	200805	1,000	500	250	-	-	2,000			1,500	-
A	200806	1,000	-	-	-	750	2,250			1,000	-
A	200807	1,000	1,000	-	-	-	4,250			2,000	-
A	200808	1,000	-	-	250	1,000	4,000			1,000	-
A	200809	1,000	-	-	-	-	5,000			250	750
A	200810	1,000	-	-	-	2,500	3,500			-	1,000
A	200811	1,000	-	-	-	-	4,500			-	1,000
A	200812	1,000	1,000	500	250	1,000	4,750				2,000
Total		12,000	3,000	1,500	500	8,250	4,750	1,500	500	8,250	4,750

Problem:- I want to create First in First Out (FIFO) calculation in above example. 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.

I would really appreciate if you can help me ASAP.

[ChrisNZ]

That's an interesting problem and I am sure someone will help shortly.

Only provided you help yourself though:

1. Provide your test data as a data step. Nobody wants to type it. See:

How to convert datasets to data steps
How to post code

2. Avoid MS Office files. Many people will not or cannot download them.

Adding an image (use the camera icon) of the wanted result would suffice here.

[andreas_lds]

To help you asap you need to do what @ChrisNZ  already mentioned: provide input-data in usable form and include the expected result as text.