proc format issue with cntlin & eexcl

[PhilipBatty]

I'm getting unpredictable behavior from a format created with CNTLIN and EEXCL='Y'.  For some ranges the upper bound is excluded (as I want), but for others it is included.

For example:

data a;

eexcl='Y';

fmtname='gpa';

do i=2 to 4 by 0.1;

     start=i;

     end=i+0.1;

     label=put(start,4.2)||'-'||put(end-0.01,4.2);

     output;

     end;

run;

proc format cntlin=a; run;

proc print data=a;

format i gpa.;

run;

data b;

do i=2,2.6,3,3.55,4;

     j=i;

     output;

     end;

run;

proc print data=b;

format j gpa.;

run;

The first proc print applies the new format to the set that created it, and it works as expected.  The second print applies the format to new data.  I get wrong formats applied to values of 2.6 and 4 (note that each of those formatted as expected in the first proc print).

An identical format created using a VALUE statement and ranges like 2-<2.1='2.00-2.09' doesn't behave like this, and PROC COMPARE thinks the 2 formats (as described in a CNTLOUT set) are identical.  Setting HLO='M' doesn't seem to affect the behavior.

Is this a bug or am I missing some obvious error in my code?

(problem occurs in Enterprise Guide 4.1 and Windows SAS 9.2)

[Peter_C]

Have you looked at the FUZZ option in PROC FORMAT?

[PhilipBatty]

Thanks for the suggestion.  I tried fuzz=0, but still get same result.  Is there some other value I should try?

[Tim_SAS · Accepted solution]

Frequently programs that use 0.1 repeatedly in a computation will run into this problem. This is because it's not possible to represent 0.1 exactly in binary, and after a few repititions the errors accumulate. Try this version of your test. Observe that the ranges created by the first data step (by adding 0.1 to the previous number) are slightly different than the values in the 2nd data step. See TS-DOC: TS-230 - Dealing with Numeric Representation Error in SAS Applications

data a;

eexcl='Y';

fmtname='gpa';

do i=2 to 4 by 0.1;

     start=i;

     i2 = i;

     end=i+0.1;

     label=put(start,4.2)||'-'||put(end-0.01,4.2);

     output;

     end;

run;

proc format cntlin=a; run;

proc print data=a;

format i gpa.;

format i2 hex16.;

run;

data b;

do i=2,2.6,3,3.55,4;

     j=i;

     j2 = j;

     output;

     end;

run;

proc print data=b;

format j gpa.;

format j2 hex16.;

run;

[PGStats]

As pointed out, the problem occurs when rounding errors accumulate. The way to avoid this is to use numbers with exact representations as much as possible so that rounding occurs only once and the FUZZ factor is enough to take care of the error. Along those lines, your example could be rewritten:

data aa;

eexcl='Y';

fmtname='gpaa';

do i=20 to 40;

     start=i*0.1;

     end=(i+1)*0.1;

     label=put(start,4.2)||'-'||put(end-0.01,4.2);

     output;

     end;

run;

proc format cntlin=aa; run;

proc print data=aa;

format i gpaa.;

run;

data b;

do i=2,2.6,3,3.55,4;

     j=i;

     output;

     end;

run;

proc print data=b;

format j gpaa.;

run;

PG

[PhilipBatty]

Thanks Tim.

I've got users specifying formatting intervals like "by0.1at0" (for ranges of width 0.1 with the lowest range staring at 0). I think if I convert the interval width to an integer, then convert back to the original scale within the loop (like PGStats also suggests) I'll avoid compounding the errors.  I may need to impose a limit to the allowed precision (e.g. "by1.000000at0" would have me counting by millions pointlessly).

Thanks everybody!