topic Re: Wrong calculated ouput in New SAS User

Wrong calculated ouput

cmemtsa — Mon, 08 Feb 2021 09:27:00 GMT

Hello!

I have a raw dataset including a column Rate with interest rates in format e.g. 3.11 and I include in a proc sql the following calculation

(Rate/100/360) as r.

The correct result is 0.0000864 yet the code outputs 0.0000900.

I have tried with formats but the result remains the same.

When I create a small testing table with same rates and the same code, the result is correct. But when I'm running the code in the initial dataset, the output is wrong.

Any ideas why?

Thank you.

Re: Wrong calculated ouput

Shmuel — Mon, 08 Feb 2021 10:04:50 GMT

It seems to me that you imported the raw data with a wrong INFORMAT.

Check the dataset created by import does it show the right and whole imported value.

Re: Wrong calculated ouput

cmemtsa — Mon, 08 Feb 2021 10:13:46 GMT

When I'm checking the column Rate in the raw dataset the value is correctly visible as 3.11.
The raw dataset is stored in main server so I cannot control the INFORMAT.

Re: Wrong calculated ouput

Shmuel — Mon, 08 Feb 2021 11:27:10 GMT

What output do you get running proc print?

proc print data=have(obs=20);
 var rate;
 format rate 5.2;
run;

Re: Wrong calculated ouput

andreas_lds — Mon, 08 Feb 2021 11:41:40 GMT

Interesting, does not happen when using a data step:

28         data _null_;
29            rate = 3.11;
30            r = rate / 100 / 360;
31            put _All_;
32         run;

rate=3.11 r=0.0000863889 _ERROR_=0 _N_=1
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

And happens in proc sql only if the create table statement is omitted, so this is just a display-problem.

Re: Wrong calculated ouput

cmemtsa — Mon, 08 Feb 2021 13:38:42 GMT

I get
3.11
2.63
5.40

etc

Re: Wrong calculated ouput

cmemtsa — Mon, 08 Feb 2021 13:41:00 GMT

I get
3.11
2.63
5.40

etc

Re: Wrong calculated ouput

cmemtsa — Mon, 08 Feb 2021 13:45:10 GMT

The error is present in proc sql either including Create table command or excluding it.

Re: Wrong calculated ouput

ballardw — Mon, 08 Feb 2021 15:47:25 GMT

@cmemtsa wrote:

Hello!

I have a raw dataset including a column Rate with interest rates in format e.g. 3.11 and I include in a proc sql the following calculation

(Rate/100/360) as r.

The correct result is 0.0000864 yet the code outputs 0.0000900.

I have tried with formats but the result remains the same.

When I create a small testing table with same rates and the same code, the result is correct. But when I'm running the code in the initial dataset, the output is wrong.

Any ideas why?

Thank you.

And what formats did you try? If you did not specify a format that would allow at least 8 decimals then you would see a result rounded to the number of decimals allowed by the format. And what SAS procedure did you use that shows the 0.0000900 with two trailing 0's?

Your "correct answer" is ROUNDED as well.

data example;
   rate=3.11;
   r = (Rate/100/360) ;
   put 'Format best5. ' r= best5.;
   put 'Format best6. ' r= best6.;
   put 'Format best7. ' r= best7.;
   put 'Format best8. ' r= best8.;
   put 'Format f9.7 '   r= f9.7;
   put 'Format best10.' r= best10.;
   put 'Format 10.8'    r= f10.8;
   put 'Format best32.' r= best32.;
run;

And what are the formats of the variables in the "initial dataset"? Perhaps you are seeing a rounded value to begin with an you have something like 3.112 not exactly 3.11.

Re: Wrong calculated ouput

Shmuel — Mon, 08 Feb 2021 18:18:43 GMT

There is no problem when using an appropriate format as demonstrated in next code:

data have;
   rate = 3.11; output;
run;
data check;
 set have;
     r = rate /360/100;
     format rate 12.8;
run;
proc sql;
 create table chk as
 select rate,
        (rate/360/100) as r 
 from have
; quit;
proc sql;
 select rate,
        (rate/360/100) as r format 12.8
 from have
; quit;

Re: Wrong calculated ouput

Tom — Mon, 08 Feb 2021 19:05:48 GMT

You mention PROC SQL. It is possible you are pulling data from some foreign database that has the variable RATE defined with some restrictive data type such as DECIMAL instead of the floating point numbers that SAS uses?

If so what happens if you convert from using integer constants to using floating point constants in your formula?

(Rate/100.0/360.0) as r

Re: Wrong calculated ouput

andreas_lds — Tue, 09 Feb 2021 06:27:36 GMT

Please show the result of proc contents using your source dataset.