Wrong calculated ouput

[cmemtsa]

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. 

[Shmuel]

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.

[cmemtsa]

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.

[Shmuel]

What output do you get running proc print?

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

[cmemtsa]

I get
3.11
2.63
5.40

etc

0 Likes

[cmemtsa]

I get
3.11
2.63
5.40

etc

[andreas_lds]

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.

[cmemtsa]

I get
3.11
2.63
5.40

etc

[cmemtsa]

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

[Shmuel]

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;

[andreas_lds]

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

[ballardw]

---

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

[Tom]

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