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;
I'm hoping to see:
x:
1
2
3
4
5
7
8
10
Next to Bottom
Bottom
@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.
Like
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" ;
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!
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.