topic PROC TABULATE order with format using a [format.] label in ODS and Base Reporting

PROC TABULATE order with format using a [format.] label

snoopy369 — Tue, 03 Sep 2019 17:41:14 GMT

I'm trying to run a tabulation where I run a bunch of single variable tables, with each variable having a custom format that is saved (notsorted) and defining the desired order.

For categorical (discrete) variables this works well; using the solution in http://support.sas.com/kb/23/847.html where we save the format with (notsorted) and use order=data on the tabulate (or on the class statement), everything is in order the way I want it.

However, for the continuous variables, we have a format that uses [best8.] as the default format, but with specific values cut out that I want to display at the bottom (imagine - values 6 and 9 have a special meaning and should go to the end). This does not seem to work with the above solution - even if I treat it like a categorical in fact, if I use the [best8.] value anywhere in the format it fails to use the correct sorting and instead shows up as the dataset order (the normal meaning of order=data ignoring the format).

Here is an example. It works fine with the 'y' variable, which is categorical, but with the 'x' continuous variable it will not sort properly.

Is there a way to make this work? Is this a bug, or expected functionality? Maybe there's a better way for me to do the format that will work?

Thanks!


proc format;
  value xf (notsorted)
   1-5=[best8.]
   7-8=[best8.]
   10=[best8.]
   9="Next to Bottom"
   6="Bottom"
 
   ;
  value yf   (notsorted)
  0 = "Top"
  1 = "Middle"
  2 = "Bottom"
  ;
  
  quit;
  
data test;  
  call streaminit(7);
  do x = 1 to 10;
    z = rand('uniform');
    y = mod(x,3);
    output;
  end;
  format x xf. y yf.;
run;

proc sort data=test;
  by z;
run;
 
%let var=x;
proc tabulate order=data;
  class &var./ preloadfmt;
  tables &var.,n;
run;

Re: PROC TABULATE order with format using a [format.] label

Reeza — Tue, 03 Sep 2019 18:37:32 GMT

What do you want to see for the 1 to 10 values in the table?

Re: PROC TABULATE order with format using a [format.] label

snoopy369 — Tue, 03 Sep 2019 18:41:01 GMT

I'm hoping to see:

Next to Bottom

Bottom

Re: PROC TABULATE order with format using a [format.] label

ballardw — Tue, 03 Sep 2019 19:30:35 GMT

@snoopy369 wrote:

I'm trying to run a tabulation where I run a bunch of single variable tables, with each variable having a custom format that is saved (notsorted) and defining the desired order.

For categorical (discrete) variables this works well; using the solution in http://support.sas.com/kb/23/847.html where we save the format with (notsorted) and use order=data on the tabulate (or on the class statement), everything is in order the way I want it.

However, for the continuous variables, we have a format that uses [best8.] as the default format, but with specific values cut out that I want to display at the bottom (imagine - values 6 and 9 have a special meaning and should go to the end). This does not seem to work with the above solution - even if I treat it like a categorical in fact, if I use the [best8.] value anywhere in the format it fails to use the correct sorting and instead shows up as the dataset order (the normal meaning of order=data ignoring the format).

Here is an example. It works fine with the 'y' variable, which is categorical, but with the 'x' continuous variable it will not sort properly.

Is there a way to make this work? Is this a bug, or expected functionality? Maybe there's a better way for me to do the format that will work?

Thanks!
proc format;
  value xf (notsorted)
   1-5=[best8.]
   7-8=[best8.]
   10=[best8.]
   9="Next to Bottom"
   6="Bottom"
 
   ;
  value yf   (notsorted)
  0 = "Top"
  1 = "Middle"
  2 = "Bottom"
  ;
  
  quit;
  
data test;  
  call streaminit(7);
  do x = 1 to 10;
    z = rand('uniform');
    y = mod(x,3);
    output;
  end;
  format x xf. y yf.;
run;

proc sort data=test;
  by z;
run;
 
%let var=x;
proc tabulate order=data;
  class &var./ preloadfmt;
  tables &var.,n;
run;

PRELOADFMT does not work that way. If you checked the log you would see a message similar to:

291
292  %let var=x;
293  proc tabulate data=work.test order=data;
294    class &var./ preloadfmt;
295    tables &var.,n;
296  run;

WARNING: The format for variable x cannot be preloaded. A finite set of formatted values cannot
         be produced from the format.  The format is not recognized, is a SAS format, calls a
         function, or contains a nested format in its definition. Preload will have no effect.
NOTE: There were 10 observations read from the data set WORK.TEST.
NOTE: PROCEDURE TABULATE used (Total process time):
      real time           0.09 seconds
      cpu time            0.03 seconds

You say that you want to see values like:

I'm hoping to see:

1
2
3
4
5
7
8
10
Next to Bottom
Bottom

So you would have to create a format that creates a finite number of values.

  value xf_alt (notsorted)
   1  ='1'  
   2  ='2'  
   3  ='3'  
   4  ='4'  
   5  ='5'  
   7  ='7'  
   8  ='8'  
   10 ='10' 
   9="Next to Bottom"
   6="Bottom"
 
   ;

Re: PROC TABULATE order with format using a [format.] label

snoopy369 — Tue, 03 Sep 2019 19:44:15 GMT

That's certainly a good point. Was hoping there was a way around it; in this case there isn't really a way to make a discrete list (due to the continuous values being, well, continuous). Thanks!

Re: PROC TABULATE order with format using a [format.] label

ballardw — Tue, 03 Sep 2019 22:19:57 GMT

@snoopy369 wrote:

That's certainly a good point. Was hoping there was a way around it; in this case there isn't really a way to make a discrete list (due to the continuous values being, well, continuous). Thanks!

You didn't really show "continuous" data. You showed discrete (integer) examples. If your values actually are continuous but you want to show '1' when the range is 0 to 1 (or something else) you can use proc format to handle that: 0-1 = '1', or if a more traditional rounding was desired 0.5 - 1.5 = '1'.

The restriction is that the DISPLAY value has to be limited to discrete values, not ranges. Note that it can be a very large number of discrete values to work with PRELOADFMT.

If you think about for a moment it will make sense. Preloadfmt is to show a constant list of results regardless of the input. So it will show a value even when no records have the value. But to have a fixed list of results you need to provide the fixed list. Another format such as BEST won't do that. It will display values up to 1.4E308 range.