Help using Base SAS procedures

proc format issue with cntlin & eexcl

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

proc format issue with cntlin & eexcl

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)


Accepted Solutions
Solution
‎05-28-2013 02:02 PM
Super Contributor
Posts: 394

Re: proc format issue with cntlin & eexcl

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;

View solution in original post


All Replies
Valued Guide
Posts: 2,175

Re: proc format issue with cntlin & eexcl

Have you looked at the FUZZ option in PROC FORMAT?

New Contributor
Posts: 3

Re: proc format issue with cntlin & eexcl

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

Solution
‎05-28-2013 02:02 PM
Super Contributor
Posts: 394

Re: proc format issue with cntlin & eexcl

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;

Respected Advisor
Posts: 4,655

Re: proc format issue with cntlin & eexcl

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

PG
New Contributor
Posts: 3

Re: proc format issue with cntlin & eexcl

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 457 views
  • 3 likes
  • 4 in conversation