topic Re: Base SAS 9 Multiplication Issue in SAS Programming

Base SAS 9 Multiplication Issue

richphillips — Thu, 03 Jan 2019 15:06:37 GMT

I'm having an issue with a simple multiplication result in SAS. The excerpt for the code is below. The calculation where I'm having a problem is NEWPREMX. In this particular example, NEWPREMB = 540 and KEYFACT2 = .62500. When I multiply that on a calculator on in Excel, I get 337.50000. SAS is returning 337.499. The problem with this is that I need to round that result to the nearest whole number and that produces 2 different results (338 for 337.5 and 337 for 337.499). The output from this is shown below the formulas. Anyone ever run into this?

NEWPREMD = ROUND(NEWPREMB * KEYFACT2);
NEWPREMX = NEWPREMB * KEYFACT2;

KEYFACT2    NEWPREMA    NEWPREMB    NEWPREMD    NEWPREMX

0.62500       540         540         337       337.499

Re: Base SAS 9 Multiplication Issue

Tom — Thu, 03 Jan 2019 15:18:18 GMT

Sounds like you have an issue with your original numbers, not the product.

If your values really where what you listed then the product would be exactly 337.5 .

Try it yourself.

2112  data test;
2113    X = 540;
2114    Y = .62500;
2115    product = x*y ;
2116    round = round(product);
2117    round2 = round(round(product,0.00001));
2118    put (_all_) (=/);
2119  run;

X=540
Y=0.625
product=337.5
round=338
round2=338

Then try making Y a little bit smaller.

2145  data test;
2146    X = 540;
2147    Y = .62500;
2148    y = y - 1E-9 ;
2149    product = x*y ;
2150    round = round(product);
2151    round2 = round(round(product,0.00001));
2152    put (_all_) (=/);
2153  run;


X=540
Y=0.624999999
product=337.49999946
round=337
round2=338

So either round your input variables to the level of precision you need. Or do your rounding of the product in two steps. First round the product to the level of precision collected. Then round it to a lower level of precision.

Re: Base SAS 9 Multiplication Issue

PaigeMiller — Thu, 03 Jan 2019 15:19:51 GMT

I am not seeing this. I get exactly 337.5 when I do the multiplication. If i format the result as 25.16, all the subsequent values after the 5 are zero.

So this implies to me that in your data, your 0.625 might not be exactly 0.625 or your 540 might not be exactly 540.

Re: Base SAS 9 Multiplication Issue

ballardw — Thu, 03 Jan 2019 16:26:34 GMT

Displayed decimal precision in all of the numeric calculation software is subject to DISPLAY settings.

Note that any Excel NUMERIC field generally defaults to 2 decimals of precision. Go to the cell, format cell and increase the number of decimals to around 9 or 10 before being sure that SAS and Excel are calculating different values. Especially since 337.499 would display as 337.5 in an Excel numeric field set to display 2 decimals.

Similarly what FORMAT do you have assigned to the variable in SAS?

Re: Base SAS 9 Multiplication Issue

richphillips — Fri, 04 Jan 2019 11:22:02 GMT

Thanks, Tom. I did figure out a bit later that I needed to round the number represented by the variable name to 5 decimal places. Once I did that, it did work as expected. I appreciate your prompt response.

Re: Base SAS 9 Multiplication Issue

richphillips — Fri, 04 Jan 2019 11:23:02 GMT

Thank you, Paige. Apparently, the .625 was the culprit as it wasn't exactly .625. I rounded this to 5 decimal places and that seemed to solve the problem. I truly appreciate you taking the time to help. Happy New Year!