Dif Statement Works Properly for Some, but not All Records

[ArmyMP]

I generated code that I can't seem to get it to work right. It seems to work fine for most records but I occasionally have records with an issue and I can't figure out what I'm doing wrong.

In looking at the following table, how do I get OP_CALC for the first record to equal the Balance_Amt and have all subsequent records calculate the dif of the previous amount? In other words, OP_Calc for COUNT 2 should be -250.03. For some reason, my code seems to be treating the second record in this example as the first.

COUNT	Applied_Amt	Balance_Amt	OP_CALC
1	0	503	503
2	250.03	252.97	252.97
3	502	-249.03	-502
4	-249.03	0	249.03
5	250	-250	-250
6	-250	0	250

[mkeintz]

We can't diagnose your code without seeing it.  Please post the code (using the popup window generated by clicking on the "running max" icon).

[ArmyMP]

DATA OP_CALC;
SET WORK.ACCT_BAL_DTL;
  by CLMEVNTID CLMACCNTBLNCDT;
  if first.CLMEVNTID then
	  COUNT=1;
	else COUNT + 1;
run;
	
DATA OP_CALC_2;
SET WORK.OP_CALC;
  by CLMEVNTID COUNT;
  if COUNT=1 AND Balance_Amt>0 then
	  OP_CALC=Balance_Amt;
	else OP_CALC=dif(Balance_Amt);
run;

[ballardw]

The Dif and Lag functions run queues. So the "last value" is the value from the last time a condition was true. If you always want the immediately previous value to be used in the assignment then create a temporary value out side of any conditional statement and then use that value as needed. Such as:

DATA OP_CALC_2;
SET WORK.OP_CALC;
  by CLMEVNTID COUNT;
  difval=dif(Balance_Amt);
  if COUNT=1 AND Balance_Amt>0 then
	  OP_CALC=Balance_Amt;
  else OP_CALC=difval;
  drop difval;
run;

[Kurt_Bremser · Accepted solution]

All functions that work with lagged values must not be called conditionally.

Restructure your code like this:

data op_calc_2;
set op_calc;
by CLMEVNTID COUNT;
OP_CALC = dif(Balance_Amt);
if COUNT = 1 and Balance_Amt > 0 then OP_CALC = Balance_Amt;
run;

[ArmyMP]

Perfect! Thank you!