topic Re: Issue with proc format in SAS Programming

Issue with proc format

puramsarat — Thu, 16 Jan 2020 10:02:07 GMT

Hi,

We have an issue with proc format

For .88000000000001 value format should give ‘VH’ as it is greater than 0.88 but it is giving out ‘H’.

Below is the code:

proc format;

value tempfmt low -< 0.205000 = 'VL'

0.205000 -< 0.410000 = 'L'

0.410000 -< 0.645000 = 'M'

0.645000 -< 0.880000 = 'H'

0.880000 - high = 'VH'

other = ' '

;

run;

data funny;

input ORIGINAL_VALUE;

format ORIGINAL_VALUE best32.30;

informat ORIGINAL_VALUE best32.30;

datalines;

.87999999999999

.88000000000000

.88000000000001

.88000000000002

.88000000000003

.88000000000004

.88000000000005

.88000000000006

.88000000000007

.88000000000008

.88000000000009

.88000000000010

.88000000000011

.88000000000100

.88000000000110

.88000000001000

.88000000001100

.88000000001110

.88000000001111

.88000000001110

.88000000011110

.88000000111110

.88000001111110

.88000011111110

.88000111111110

.88001111111110

.88011111111110

.88111111111110

;

run;

data funny;

set funny;

FORMAT_VALUE= put(ORIGINAL_VALUE, tempfmt.);

run;

proc print data=work.funny noobs;

format ORIGINAL_VALUE comma32.30;

run;

Output is:

ORIGINAL_VALUE	FORMAT_VALUE
0.879999999999990000000000000000	H
0.880000000000000000000000000000	VH
0.880000000000010000000000000000	H
0.880000000000020000000000000000	H
0.880000000000030000000000000000	H
0.880000000000040000000000000000	H
0.880000000000050000000000000000	H
0.880000000000060000000000000000	H
0.880000000000070000000000000000	H
0.880000000000080000000000000000	H
0.880000000000090000000000000000	H
0.880000000000100000000000000000	H
0.880000000000110000000000000000	H
0.880000000001000000000000000000	H
0.880000000001100000000000000000	VH
0.880000000010000000000000000000	VH
0.880000000011000000000000000000	VH
0.880000000011100000000000000000	VH
0.880000000011110000000000000000	VH
0.880000000011100000000000000000	VH
0.880000000111100000000000000000	VH
0.880000001111100000000000000000	VH
0.880000011111100000000000000000	VH
0.880000111111100000000000000000	VH
0.880001111111100000000000000000	VH
0.880011111111100000000000000000	VH
0.880111111111100000000000000000	VH
0.881111111111100000000000000000	VH
0.881111111111100000000000000000	VH

Thank you,

Sarat.

Re: Issue with proc format

yabwon — Thu, 16 Jan 2020 10:29:52 GMT

Hi @puramsarat ,

You entered the "numerical precision errors" ground.

Check out the documentation for the FUZZ= option in the proc format:

https://documentation.sas.com/?docsetId=proc&docsetTarget=p1upn25lbfo6mkn1wncu4dyh9q91.htm&docsetVersion=9.4&locale=en#n0qhedcd1ls7ren1hn86ntotxrai

All the best

Bart

Re: Issue with proc format

ErikLund_Jensen — Thu, 16 Jan 2020 11:24:09 GMT

Hi @puramsarat

As @yabwon writes, you need to specify a fuzz value. It works with this value:

*fuzz =   0.000000000000001
*value = 0.88000000000001
;
proc format;
	value tempfmt (fuzz=0.000000000000001) 
		low -< 0.205000 = 'VL'
		0.205000 -< 0.410000 = 'L'
		0.410000 -< 0.645000 = 'M'
		0.645000 -< 0.880000 = 'H'
		0.880000 - high = 'VH'
		other = ' '
;
run;

Re: Issue with proc format

FreelanceReinh — Thu, 16 Jan 2020 12:04:40 GMT

Hi @puramsarat,

I would prefer fuzz=0 in this situation -- following the advice in the documentation (see link in yabwon's post): "A best practice is to use FUZZ=0 when you use the < exclusion operator with numeric formats."

Also, I guess that in practice values such as .87999999999999 or .88000000000001 more often than not are the result of rounding errors, mostly linked to numeric representation issues. I would not rely on them being less or greater than 0.88. Here's an example:

data _null_;
a=1234.88;
v=a-1234;
put v best16.;
run;

Result (SAS 9.4 under Windows):

0.88000000000011

Obviously, the mathematically correct result would be 0.88 and it would be obtained by using the ROUND function appropriately, e.g.

v=round(a-1234, 1e-9);

With "clean" numeric values (as obtained with the ROUND function) your format would much less likely produce incorrect results, even with the default FUZZ= value (1E-12).

Re: Issue with proc format

puramsarat — Fri, 17 Jan 2020 01:39:12 GMT

Thank you for
Bart @yabwon, ErikLund_Jensen and FreelanceReinhard for providing me the solution with quick turnaround. FUZZ option fixed the issue.