topic Re: Flooring array variable in SAS Programming

Flooring array variable

Sam13 — Sat, 11 Jul 2020 09:18:49 GMT

I am trying to floor an array variable LGD to a value of 10% , i've tried two different methods but they both did not work, i do not see why! the code does not generate an error but it just does not floor the array variable, below are two methods i've tried (max function & if then statement), can anyone tell where is the problem please? (note the code is working fine without the flooring issue, i am only including the relevant part)

Method 1(max function)

DO I = 1 TO 100

LGD[I] = max(ROUND(PPDA[I]* ((EAD[I]-RECOVERY[I])/EAD[I]),0.000001),0.10);
END;

Method 2(if then statement)

DO I = 1 TO 100

LGD[I] = ROUND(PPDA[I]* ((EAD[I]-RECOVERY[I])/EAD[I]),0.000001);
if LGD[I] <0.10 then LGD[I]=0.10 else LGD[I]=LGD[I] ;
END;

Re: Flooring array variable

Jagadishkatam — Sat, 11 Jul 2020 09:32:36 GMT

hope you used the array statement.

Could you please post the log as well , also sample data

Re: Flooring array variable

Sam13 — Sat, 11 Jul 2020 09:37:45 GMT

@Jagadishkatam

As i mentioned the codes are working fine before i floored them using the max statement in method 1 or the if-then ststement in method 2, i could not post the entire code or data for confidentiality issue, but there was no problem with the code. I am so frustrated why these two methods are not flooring, there is no error generated at all!!!!!

Re: Flooring array variable

ballardw — Sat, 11 Jul 2020 10:27:24 GMT

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the <> to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

One thing, entire data step code is really the way to go because then we can see if there might be something else causing issues.

Second, data: input and expected output. There really is very little in the way of diagnosis that can be done without data values.

Re: Flooring array variable

Kurt_Bremser — Sat, 11 Jul 2020 10:48:34 GMT

What are the results you get without the MAX() function? Are they in fact smaller than 0.1?

Anyway, please post example data, and the complete log of the step.

Use this button to post the log:

and the "little running man" right next to it for the example datalines data step.

Re: Flooring array variable

ChrisNZ — Sat, 11 Jul 2020 11:27:16 GMT

Flooring means using the min() function. Not the max () function.

You want at least 10%. 10% is the minimum.

Re: Flooring array variable

Sam13 — Sat, 11 Jul 2020 11:42:12 GMT

@Kurt_Bremser

Thanks for your reply. the result you get is the same without doing the max function meaning the max function or even the other methods are doing nothing, the values are below 0.1 for sure and there is no error whatsoever i see the max statement in there with no error, it is so strange! i am not new to SAS that is why i am so frustrated as i can see no problem in the code but getting no flooring results!!

Re: Flooring array variable

PaigeMiller — Sat, 11 Jul 2020 11:45:36 GMT

@Sam13

We're frustrated too. People have asked to see your LOG and your data, and you haven't provided those. Providing the LOG as requested and (a portion of) the data as requested is the fastest way to solve this problem.

Re: Flooring array variable

Sam13 — Sat, 11 Jul 2020 11:49:04 GMT

@ballardw

Here is part of the code:

Data table1;

set table2;



DO I = 1 TO (REMAINING_TERM + 2); 


DEFFTV[I] = PUT(EAD[I] / ((CURRENT_COLLATERAL_VALUE/CURR_HPI)*HPI[I]),FTVBAND.);


DEF_FTV_BAND = DEFFTV[I];

CALL MISSING(PPD);

IF DEF_FTV_BAND NE 0 THEN DO;

RC = PPDH.FIND(KEY:DEF_FTV_BAND);

PPDA[I] = PPD; 

VAS[I] = ((CURRENT_COLLATERAL_VALUE/CURR_HPI)*HPI[I+TTS]);

RECOVERY[I] = MIN(((1-FSD)*VAS[I])-MAX(FIXED_RECOVERY_COST,(VAS[I]*'Variable_Recovery Cost'n)),EAD[I])/(SUM(1,EPR)**(TTS/12));

** Final LGD value; 
LGD[I] = max(ROUND(PPDA[I]* ((EAD[I]-RECOVERY[I])/EAD[I]),0.000001),0.10);

END;

** IF FTV is missing, 0 or negative then LGD is set to missing; 
ELSE LGD[I] = .;

END;

RUN;

Re: Flooring array variable

Sam13 — Sat, 11 Jul 2020 11:51:22 GMT

@ChrisNZ

No that is not correct, with MAX(a,b) the result would be whichever higher, so if max(0.05,0.10) then i want 0.10...so max is the correct function

Re: Flooring array variable

Kurt_Bremser — Sat, 11 Jul 2020 11:56:42 GMT

You have a FIND() method call to an undefined hash object; this code CANNOT WORK.

Please post:

EXAMPLE DATA in a data step with datalines. DO NOT SKIP THIS STEP!

The COMPLETE LOG of your step.

@Sam13 wrote:

@ballardw

Here is part of the code:

Data table1;

set table2;



DO I = 1 TO (REMAINING_TERM + 2); 


DEFFTV[I] = PUT(EAD[I] / ((CURRENT_COLLATERAL_VALUE/CURR_HPI)*HPI[I]),FTVBAND.);


DEF_FTV_BAND = DEFFTV[I];

CALL MISSING(PPD);

IF DEF_FTV_BAND NE 0 THEN DO;

RC = PPDH.FIND(KEY:DEF_FTV_BAND);

PPDA[I] = PPD; 

VAS[I] = ((CURRENT_COLLATERAL_VALUE/CURR_HPI)*HPI[I+TTS]);

RECOVERY[I] = MIN(((1-FSD)*VAS[I])-MAX(FIXED_RECOVERY_COST,(VAS[I]*'Variable_Recovery Cost'n)),EAD[I])/(SUM(1,EPR)**(TTS/12));

** Final LGD value; 
LGD[I] = max(ROUND(PPDA[I]* ((EAD[I]-RECOVERY[I])/EAD[I]),0.000001),0.10);

END;

** IF FTV is missing, 0 or negative then LGD is set to missing; 
ELSE LGD[I] = .;

END;

RUN;

Re: Flooring array variable

Sam13 — Sat, 11 Jul 2020 12:02:40 GMT

@Kurt_Bremser

the code is not new, it was functioning for weeks and all is fine, I was asked to floor the LGD variable and I only added the max function, so FIND() method or any of the rest were already there and working absolutely fine, I only need to floor the LGD[I] values, that is all

Re: Flooring array variable

Sam13 — Sat, 11 Jul 2020 12:04:24 GMT

@Kurt_Bremser

here is part of the log:

 MPRINT(RUN_ALL):   DO I = 1 TO (REMAINING_TERM + 2);
 MPRINT(RUN_ALL):   DEFFTV[I] = PUT(EAD[I] / ((CURRENT_COLLATERAL_VALUE/CURR_HPI)*HPI[I]),FTVBAND.);
 MPRINT(RUN_ALL):   ** Forecasted FTV at default calculated to merge on correct PPD;
 MPRINT(RUN_ALL):   DEF_FTV_BAND = DEFFTV[I];
 MPRINT(RUN_ALL):   CALL MISSING(PPD);
 MPRINT(RUN_ALL):   IF DEF_FTV_BAND NE 0 THEN DO;
 MPRINT(RUN_ALL):   RC = PPDH.FIND(KEY:DEF_FTV_BAND);
 MPRINT(RUN_ALL):   PPDA[I] = PPD;
 MPRINT(RUN_ALL):   ** Valuation at sale (VAS) calculated to work out total recoverable amount before costs;
 MPRINT(RUN_ALL):   VAS[I] = ((CURRENT_COLLATERAL_VALUE/CURR_HPI)*HPI[I+TTS]);
 MPRINT(RUN_ALL):   ** Expected reovery amount after forced sale discount and reocovery costs applied;
 MPRINT(RUN_ALL):   RECOVERY[I] = MIN(((1-FSD)*VAS[I])-MAX(FIXED_RECOVERY_COST,(VAS[I]*'Variable_Recovery 
 Cost'n)),EAD[I])/(SUM(1,EPR)**(TTS/12));
 MPRINT(RUN_ALL):   ** Final LGD value;
 MPRINT(RUN_ALL):   LGD[I] = max(ROUND(PPDA[I]* ((EAD[I]-RECOVERY[I])/EAD[I]),0.000001),0.05);
 MPRINT(RUN_ALL):   END;
 MPRINT(RUN_ALL):   ** IF FTV is missing, 0 or negative then LGD is set to missing;
 MPRINT(RUN_ALL):   ELSE LGD[I] = .;
 MPRINT(RUN_ALL):   END;
 MPRINT(RUN_ALL):   RUN;

Re: Flooring array variable

Kurt_Bremser — Sat, 11 Jul 2020 12:36:01 GMT

Since you do not show any real willingness of helping us to help you (no data, incomplete log - this was the first time you showed that this is part of a macro!), I'll post this once to prove that "flooring" with the MAX() function works:

data test;
input x;
format x y 5.2;
y = max(x,0.1);
datalines;
0.12
0.09
;

so it is in the other code you did not show us, or in the data, or both. Make your choice, I'm outta here.

Re: Flooring array variable

FreelanceReinh — Sat, 11 Jul 2020 13:13:07 GMT

Hi @Sam13,

Your "part of the log" does not show any NOTEs and other messages which could help diagnosing the issue. For example, I'm pretty sure there is a note

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). ...

because the value of PUT(..., FTVBAND.) (line 2) is a character value, but eventually this value is compared to the numeric value 0 in the IF condition, which in my opinion is questionable programming style.

That said, the log shows that the second argument of the MAX function has changed from 0.1 (in your earlier posts) to 0.05. So, with this code values <0.1 would be no surprise.

If I had to investigate the issue, I would insert a diagnostic PUT statement after the ELSE statement, e.g.:

  if .z<lgd[i]<0.05 then do;
    put _all_;
    stop;
  end;

This might still be insufficient because if the arrays PPDA etc. were _temporary_ arrays, their values would not be displayed (then you'd need to list the relevant array elements in the PUT statement once you know their index I, e.g., PPDA[17]). But we don't know what type the arrays are because you haven't shown any ARRAY statements so far.

Re: Flooring array variable

ChrisNZ — Sun, 12 Jul 2020 01:24:00 GMT

My bad. You are right of course. That will teach me not to reply at midnight. I hope... 🙂

Re: Flooring array variable

ballardw — Sun, 12 Jul 2020 15:14:08 GMT

@Sam13 wrote:

@ballardw

Here is part of the code:

Data table1;

set table2;



DO I = 1 TO (REMAINING_TERM + 2); 


DEFFTV[I] = PUT(EAD[I] / ((CURRENT_COLLATERAL_VALUE/CURR_HPI)*HPI[I]),FTVBAND.);


DEF_FTV_BAND = DEFFTV[I];

CALL MISSING(PPD);

IF DEF_FTV_BAND NE 0 THEN DO;

RC = PPDH.FIND(KEY:DEF_FTV_BAND);

PPDA[I] = PPD; 

VAS[I] = ((CURRENT_COLLATERAL_VALUE/CURR_HPI)*HPI[I+TTS]);

RECOVERY[I] = MIN(((1-FSD)*VAS[I])-MAX(FIXED_RECOVERY_COST,(VAS[I]*'Variable_Recovery Cost'n)),EAD[I])/(SUM(1,EPR)**(TTS/12));

** Final LGD value; 
LGD[I] = max(ROUND(PPDA[I]* ((EAD[I]-RECOVERY[I])/EAD[I]),0.000001),0.10);

END;

** IF FTV is missing, 0 or negative then LGD is set to missing; 
ELSE LGD[I] = .;

END;

RUN;

Would generate a bunch of undefined array references.

Where are the arrays defined?