topic Re: Dif Statement Works Properly for Some, but not All Records in SAS Enterprise Guide

Dif Statement Works Properly for Some, but not All Records

ArmyMP — Tue, 19 May 2020 18:12:23 GMT

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

Re: Dif Statement Works Properly for Some, but not All Records

mkeintz — Tue, 19 May 2020 18:24:32 GMT

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).

Re: Dif Statement Works Properly for Some, but not All Records

ArmyMP — Tue, 19 May 2020 18:31:32 GMT

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;

Re: Dif Statement Works Properly for Some, but not All Records

ballardw — Tue, 19 May 2020 19:20:55 GMT

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;

Re: Dif Statement Works Properly for Some, but not All Records

Kurt_Bremser — Tue, 19 May 2020 19:25:07 GMT

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;

Re: Dif Statement Works Properly for Some, but not All Records

ArmyMP — Tue, 19 May 2020 21:00:45 GMT

Perfect! Thank you!