topic Re: SAS formula with logs not giving correct values in SAS Programming

SAS formula with logs not giving correct values

Asek — Wed, 24 Jul 2019 13:46:55 GMT

Hi all,

I'm trying to put this Excel formula into SAS version 9.4 =POWER(10,-0.805+0.282*B4+0.078*B5+0.525*LOG(B6,10)+0.521*LOG(B7,10)-0.454*LOG((B7/B8),10))

In Excel I get percents ranging from 0.51 to 32.67 which make sense. However in SAS I get values ranging from 1.34242E-21 to 110.7295.

This is what I have written in a SAS data step

LINS1 = log10(INS1); 
LAST1 = log10(AST1); 
LASTdALT = log10(AST1/ALT1);

LiverFat_P = (-0.805 + (0.282*MetS) + (0.078*T2DM) + (0.525*LINS1) + (0.521*LAST1) - (0.454*LASTdALT))**10;

In my log I get a note saying division by 0 issues were detected at this formula and these values were set to missing, but no other apparent issues. Any ideas on what's going on and how to fix it?

Many thanks!

Re: SAS formula with logs not giving correct values

PaigeMiller — Wed, 24 Jul 2019 13:51:20 GMT

@Asek wrote:

This is what I have written in a SAS data step
LINS1 = log10(INS1); 
LAST1 = log10(AST1); 
LASTdALT = log10(AST1/ALT1);

LiverFat_P = (-0.805 + (0.282*MetS) + (0.078*T2DM) + (0.525*LINS1) + (0.521*LAST1) - (0.454*LASTdALT))**10;
In my log I get a note saying division by 0 issues were detected at this formula and these values were set to missing, but no other apparent issues. Any ideas on what's going on and how to fix it?

Please actually look at the data in your SAS data set (not in Excel) to see if there are actual zeros in a denominator. Let us know what you find.

Re: SAS formula with logs not giving correct values

Asek — Wed, 24 Jul 2019 14:25:22 GMT

Thank you for the suggestion Paige. There was one 0 and I set it to missing. This removes the "division by 0" note from my log but does not solve the issue of the wrong values I'm getting. They are still the same.

Re: SAS formula with logs not giving correct values

PaigeMiller — Wed, 24 Jul 2019 14:29:35 GMT

I think you need to continue simple debugging steps. Does B4 map to MetS? Are the data values in Excel the same as in SAS? etc.

Re: SAS formula with logs not giving correct values

Asek — Wed, 24 Jul 2019 15:04:21 GMT

I spent hours checking everything I could think of yesterday - including checking that the data in the individual observations where I was getting wrong SAS values was the same in SAS and Excel (it is). I'm hoping for additional ideas of what to check. I'm wondering whether it's related to decimal places used in the calculation?

Re: SAS formula with logs not giving correct values

PGStats — Wed, 24 Jul 2019 15:09:42 GMT

I think your expression should be

LiverFat_P = 10**(-0.805 + (0.282*MetS) + (0.078*T2DM) + (0.525*LINS1) + (0.521*LAST1) - (0.454*LASTdALT));

Re: SAS formula with logs not giving correct values

Asek — Wed, 24 Jul 2019 15:21:33 GMT

Yes, that worked. Thank you! I knew it was going to be something simple.