topic Re: proc format issue with cntlin & eexcl in SAS Procedures

proc format issue with cntlin & eexcl

PhilipBatty — Tue, 28 May 2013 13:49:59 GMT

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)

Re: proc format issue with cntlin & eexcl

Peter_C — Tue, 28 May 2013 17:33:41 GMT

Have you looked at the FUZZ option in PROC FORMAT?

Re: proc format issue with cntlin & eexcl

PhilipBatty — Tue, 28 May 2013 17:58:00 GMT

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

Re: proc format issue with cntlin & eexcl

Tim_SAS — Tue, 28 May 2013 18:02:00 GMT

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;

Re: proc format issue with cntlin & eexcl

PGStats — Tue, 28 May 2013 18:58:05 GMT

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;

Re: proc format issue with cntlin & eexcl

PhilipBatty — Tue, 28 May 2013 19:04:17 GMT

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!