topic PROC ODSTABLE, format not being applied to column in ODS and Base Reporting

PROC ODSTABLE, format not being applied to column

PhilC — Wed, 27 Jan 2021 19:45:46 GMT

Please check me on this code. What am I missing? Is this a bug?

Using SAShelp.Cars, I'm creating a character format, $MAKE, which maps the first three (3) characters of the column Make to the full text. Then I'm attempting to create a table using ODSTABLE giving the 3 character data to the procedure and I'm wanting the procedure to transform that data by the format. This is suppose to work like the example here: SAS Help Center: Defining Variables with the COLUMN Statement -- I think. (??)

options nofmterr nocenter;

data cntlin (keep=FMTNAME end start label type );
  set SAShelp.Cars (obs=50);
    by Make ;
  if first.Make then do;
    FMTNAME="Make";
    type="C";
    length start end $3;
      start=substr(Make,1,3);
      format start $Make.;
    end=start;
    label=Make;
    output;
  end;
proc format cntlin=&sysLast;
run;

proc odstable data=cntlin(Keep=start ) PAGEBREAK=No;
  column   start ;
  define start;
    format=$make.;
  end;
run;

I get this output like this:

start Acu Aud BMW Bui

I expected this:

start Acura Audi BMW Buick

Re: PROC ODSTABLE, format not being applied to column

ballardw — Wed, 27 Jan 2021 20:45:17 GMT

First mistake:

557  data cntlin (keep=FMTNAME end start label type );
558    set SAShelp.Cars (obs=50);
559      by Make ;
560    if first.Make then do;
561      FMTNAME="Make";
562      type="C";
563      length start end $3;
564        start=substr(Make,1,3);
565        format start $Make.;
                        ------
                        48
ERROR 48-59: The format $MAKE was not found or could not be loaded.

566      end=start;
567      label=Make;
568      output;
569    end;

The first time this data step runs the format is not defined and creates an error and no data set.

Next you didn't look at all the documentation related to setting formats. If you don't specify a format_width option the length of the variable seems to be the default display width. So you override that with a Format_width:

proc odstable data=cntlin(Keep=start ) PAGEBREAK=No;
  column   start ;
  define start;
    
    format=$make10.;

  end;
run;

You may need to consider specifying decimals when dealing with numeric values.

Re: PROC ODSTABLE, format not being applied to column

PhilC — Wed, 27 Jan 2021 21:44:06 GMT

Hey, don't give me that kind of stuff. What about running options nofmterr? I did that on purpose @ballardw!

...Format width... huh. I have stared at this for a day! I ruled it out at some point, falsely. Well I guess I deserve it.

OK I see, I was looking at the datasets and inside the datasets the formats displayed longer than three characters there. Now I see. The default width of Make is in fact 5, if you check. So PROC ODSTABLE does have a weird behavior. It is as if the procedure is confusing the default width of the format with the character length of the column. I have a point, do I not?

Re: PROC ODSTABLE, format not being applied to column

ballardw — Wed, 27 Jan 2021 23:38:29 GMT

While I think that specifying a the width in the font may be the overall easiest you could also use:

proc odstable data=cntlin(Keep=start ) PAGEBREAK=No;
  column   start ;
  define start;
    
    format=$make.;
    font_width=10;
  end;
run;

Or make the variable with a length at least as great as the expected display width.

This does work for your expected output:

data cntlin (keep=FMTNAME end start label type );
  set SAShelp.Cars (obs=50);
    by Make ;
  if first.Make then do;
    FMTNAME="Make";
    type="C";
    length start end $15;
      start=substr(Make,1,3);
    end=start;
    label=Make;
    output;
  end;
run;
proc format cntlin=cntlin ;
run;

proc odstable data=cntlin(Keep=start ) PAGEBREAK=No;
  column   start ;
  define start;
    
    format=$make.;
  end;
run;

Even though the defined length of Start is 15, with the values assigned of length 3 the default width is long enough for the 5 character labels (all that were selected for making the format).

Re: PROC ODSTABLE, format not being applied to column

PhilC — Thu, 28 Jan 2021 12:40:37 GMT

You're definitely right, but I'm going to report the bug. Thanks for your input.