Trying to use incorrect data and I need help

Reply
Occasional Contributor
Posts: 16

Trying to use incorrect data and I need help

I have the following code

create table waiver as

select tbraccd_pidm as pidm_key, tbraccd_amount format=8.2 informat=8.2, tbraccd_detail_code,

tbbdetc_desc, tbraccd_term_code

from ora.tbraccd, ora.tbbdetc

where tbraccd_term_code in ("20&year1.30",/*"20&year1.03",*/"20&year1.40",/*"20&year1.04",*/"20&year2.10",/*"20&year2.01",*/

"20&year2.20",/*"20&year2.02"*/) and

tbraccd_detail_code = tbbdetc_detail_code and

tbraccd_detail_code in ('EFTV','WAPG','WCHS','WCNY','WDEN','WDEP','WDE4','WDE5','WDIN','WDIS','WEMN','WEMP','WFOS',

'WHMS','WHOM','WHON','WICE','WICM','WNGC','WNGD','WNNG','WNRN','WPRN','WPRT','WRMT',

'WSNN','WSNR','WSPN','WSPS','WSWP','WUDI','WUDO')and

tbraccd_pidm not in (220322,232937) /* VA board & not student*/

order by tbraccd_pidm, tbraccd_detail_code, tbraccd_term_code;

 

Which I follow up with the next batch of code:

 

data waiver2(keep=pidm_key tw_amount tbraccd_detail_code tbraccd_term_code)/debug;

retain tw_amount;

format tw_amount 8.2;

set waiver;

by pidm_key tbraccd_detail_code tbraccd_term_code;

if first.tbraccd_term_code then tw_amount=0;

tw_amount = tbraccd_amount + tw_amount;

if last.tbraccd_term_code and tw_amount>= 0.01;

run;

 

Somehow some of the tbraccd_amount data looks like 1456.100000000000000000003 which makes an issue when I attempt to add the data together & compare it to the output data that I pulled out before changes to my program. How can I fix the programs to only get data that is 1456.10?  Thanks.

Grand Advisor
Posts: 10,223

Re: Trying to use incorrect data and I need help

Round the values, possibly using

Round(tbraccd_amount,0.01) as tbraccd_amount

 

or one of the truncation functions like FLOOR

Respected Advisor
Posts: 3,837

Re: Trying to use incorrect data and I need help

@HarfordKaren

That's an oldie but a goodie and I'm sure there are many programs out there with the same issue - whether detected or not...

 

This precision issue is something you must be aware of and code for when moving floating point numbers between platforms (not only when working with SAS but in general). Here some more background:

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#n1gv8bfdnhvy9yn1cj...

http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#p0ji1unv6thm0dn1gp4t...

 

To get around this either:

A) Round the value in SAS permanently (=change the stored value). Something like this should work:

myvar=round(myvar,0.00000001);

 

B) Use the compfuzz() or round() function for comparisons

https://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p0ifledavu...

https://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p0tj6cmga7...

 

 

Ask a Question
Discussion stats
  • 2 replies
  • 231 views
  • 1 like
  • 3 in conversation