topic Re: Trying to use incorrect data and I need help in SAS Programming

Trying to use incorrect data and I need help

HarfordKaren — Fri, 23 Oct 2015 19:16:47 GMT

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.

Re: Trying to use incorrect data and I need help

ballardw — Fri, 23 Oct 2015 19:26:34 GMT

Round the values, possibly using

Round(tbraccd_amount,0.01) as tbraccd_amount

or one of the truncation functions like FLOOR

Re: Trying to use incorrect data and I need help

Patrick — Fri, 23 Oct 2015 23:14:17 GMT

@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#n1gv8bfdnhvy9yn1cj8g4di2eqg7.htm

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

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#p0ifledavu3rv1n10gclzxbyy0ul.htm

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