BookmarkSubscribeRSS Feed
ProblemSolver
Fluorite | Level 6

Hi everyone,

 

I'm kind of stuck on a problem with proc tabulate and it would be great if someone could help me out. I've read in various papers as well as in the official Base SAS Procedures guide that it is possible to create subtotals in proc tab by using user-defined multilabel formats with the options mlf, preloadfmt, and order=data. I believe I have set up the format and the proc tab in the correct way, but I get the following error:

"WARNING: MLF conflicts with PRELOADFMT in class variable bucketab.  It is ignored"

And thus the subtotal rows are not displayed. Here's my code:

 

 

proc format;
	value bucketfmt (NOTSORTED MULTILABEL)
				0   = 	"  0 to 5"
				1   = 	"  6 to 30"
				0-1 =   "0 to 30"
				2   = 	"  31 to 60"
				3   = 	"  61 to 90"
				4   = 	"  91 to 120"
				5   = 	"  121+"
				2-5 =   "31+"
				;
run;
proc tabulate data = de_term_6 NOSEPS;
		class bucketab / mlf preloadfmt order=data MISSING;
		class product stage;
		var total_osb;
		table bucketab ALL,
			  product*stage*(N*F=comma8. total_osb*F=NLMNLEUR20.2 PCTN*F=pctfmt8.2 PCTSUM*total_osb=""*F=pctfmt8.2)
			  ALL*(N*F=comma8. total_osb*F=NLMNLEUR20.2 PCTN*F=pctfmt8.2 PCTSUM*total_osb=""*F=pctfmt8.2)
			  / BOX = "----------";
		label bucketab = "" total_osb = "€";
		keylabel N = "#" SUM = "" PCTN = "%#" PCTSUM = "%€" ALL = "Total";
		format bucketab bucketfmt.;
		where contract_status = "Active" & stage != "SALVAGE" & total_osb > 0;
		title "---------------";
		title2 "July 2017"; 
	run;

I've tried using printmiss as an option on the table statement, that resulted in the 30+ row being printed, but without any values. I don't believe this options should be necessary in any case.

 

Any help is much appreciated.

 

7 REPLIES 7
Reeza
Super User
What happens if you remove the PRELOADFMT? Or do you want that for all levels to be included? It's nice that you included all your code, but without data we can't run it to test anything.
ProblemSolver
Fluorite | Level 6

Thanks for the quick answer. If I remove PRELOADFMT then the rows are shown, but ordered ascending according to the label. A workaround would be to put a, b, c before the numbers in the labels, but that's ugly and not ideal.

 

I cannot provide any data, but it's quite basic the class variable product and stage can be left out for this purpose, the distribution of bucketab can be seen in the proc format and total_osb is the numeric analysis variable.

ballardw
Super User

@ProblemSolver wrote:

Thanks for the quick answer. If I remove PRELOADFMT then the rows are shown, but ordered ascending according to the label. A workaround would be to put a, b, c before the numbers in the labels, but that's ugly and not ideal.

 

I cannot provide any data, but it's quite basic the class variable product and stage can be left out for this purpose, the distribution of bucketab can be seen in the proc format and total_osb is the numeric analysis variable.


Resulting order of multilabel formats depends a bit on the definition of the format and then the options in tabulate.

Here are some examples that may help:

/* To demonstrate how the order of definition affects appearance in
   multilabel formats. Also appearance options to show the spaces to
   get the indent as desired and fix column widths.
   And investigate whether class level format based style overrides work
with MLF in proc tabulate.  Result: NO.
*/
proc format library=work;
value accidentl (multilabel notsorted)
1-5 = 'Accidents'
1-3 = ' Transport accidents'
1 = '   Motor vehicle accidents'
2 = '   Water, air, and space'
3 = '   Other land transport accidents'
4-5 = ' Nontransport accidents'
5 = '   Fishing'
;
value accidentr (multilabel notsorted)
1-5 = 'Accidents'
1-3 = ' Transport accidents'
4-5 = ' Nontransport accidents'
1 = '   Motor vehicle accidents'
2 = '   Water, air, and space'
3 = '   Other land transport accidents'
5 = '   Fishing'
;
value accidents (multilabel notsorted)
1 = '   Motor vehicle accidents'
2 = '   Water, air, and space'
3 = '   Other land transport accidents'
5 = '   Fishing'
1-5 = 'Accidents'
1-3 = ' Transport accidents'
4-5 = ' Nontransport accidents'
;
value mf
1 = "Male"
2 = "Female"
;
value mycolor
1='white'
2='red'
3='green'
4='blue'
5='orange'
6='purple'
;
value accsimple
1 = 'Motor vehicle accidents'
2 = 'Water, air, and space'
3 = 'Other land transport accidents'
4 = 'Nontransport accidents'
5 = 'Fishing'
;
value MyColorl (multilabel notsorted)
1-5 = 'white'
1-3 = 'red'
1 = 'blue'
2 = 'orange'
3 = 'pink'
4-5 = 'purple'
5 = 'black'
;
run;

/* populate a dataset to display */
/* This specifically does NOT generate any data for FISHING above*/
/* to display the behavior of the options below in those cases. */
data junk; 
do i=1 to 50;
type = round(4*ranuni(1234)+.5);
sex = round(2*ranuni(3455)+.5);
output;
end; 
run;


/* Notice that before we get here the data is NOT sorted */
/* in any manner!!!! */
title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidentl';
proc tabulate data=junk order=data ;
   class type / mlf PRELOADFMT;
   /*ASIS preserves the leading spaces in the format, Cellwidth here is optional
     for this demo. These will ONLY affect ODS output such as HTML, RTF or PDF
     not the OUTPUT window
  */
   classlev type/ style=[asis=on cellwidth=1.5in];
   class sex;
   /* the formatted values of SEX normally take up different lengths, this fixes
      the column widths to be the same for both headers. May cause interesting
      appearances with large numbers of displayed digits if requested in formats*/
   classlev sex/ style=[cellwidth=.5in];
   /* to get the ALL to have the same width as SEX need to specify this way*/
   table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8.
   / printmiss misstext='0';
   format type accidentl. sex mf.;
run;title;

title 'Option simple format and classlev background color';
proc tabulate data=junk order=data ;
   class type / ;
   /*ASIS preserves the leading spaces in the format, Cellwidth here is optional
     for this demo. These will ONLY affect ODS output such as HTML, RTF or PDF
     not the OUTPUT window
  */
   classlev type/ style=[asis=on cellwidth=1.5in background=mycolor.];
   class sex;
   /* the formatted values of SEX normally take up different lengths, this fixes
      the column widths to be the same for both headers. May cause interesting
      appearances with large numbers of displayed digits if requested in formats*/
   classlev sex/ style=[cellwidth=.5in];
   /* to get the ALL to have the same width as SEX need to specify this way*/
   table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8.
   / row=float misstext='0';
   format type accsimple. sex mf.;
run;title;

title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidentl classlev background';
proc tabulate data=junk order=data;
   class type / mlf PRELOADFMT;
   classlev type/ style=[asis=on cellwidth=1.5in background=mycolor.];
   class sex;
   classlev sex/ style=[cellwidth=.5in];
   table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8.
   / printmiss misstext='0';
   format type accidentl. sex mf.;
run;title;
title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidents';
proc tabulate data=junk order=data;
   class type / mlf PRELOADFMT;
   classlev type/ style=[asis=on cellwidth=1.5in];
   class sex;
   classlev sex/ style=[cellwidth=.5in];
   table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8.
   / printmiss misstext='0';
   format type accidents. sex mf.;
run;title;
 

ballardw
Super User

You may have to try the CLASSDATA optional data set to contain the combinations of the class variables in the output.

 

You may also want to try the proc tabulate without preloadfmt just to see if the ALL works the way you want. My experience that multilables and All sometimes don't quite provide what i wanted/expected.

ProblemSolver
Fluorite | Level 6

ALL works fine without the option PRELOADFMT and the subtotal rows contain the correct values as well.

 

How exactly would you suggest CLASSDATA? Not sure how I would include the subtotals there.

ballardw
Super User

@ProblemSolver wrote:

ALL works fine without the option PRELOADFMT and the subtotal rows contain the correct values as well.

 

How exactly would you suggest CLASSDATA? Not sure how I would include the subtotals there.


The last two examples in the other other post I made show use of MLF and PRELOADFMT but your needs may not match those.

ProblemSolver
Fluorite | Level 6

Thanks again for your help.

 

I've adapted the example you posted to create a sample dataset so that I can show outputs. Whatever I do though, I still get that same warning whenever I include both the MLF and PRELOADFMT options.

 

WARNING: MLF conflicts with PRELOADFMT in class variable bucket.  It is ignored

 

Here is the code:

data junk; 
	do i=1 to 50;
	bucket = round(5*ranuni(1234)+.5);
	osb = round(100*ranuni(1234)+.5);
	output;
end; 
run;

proc tabulate data = junk order=data;
	class bucket / mlf preloadfmt;
	classlev bucket / style=[asis=on];
	var osb;
	table bucket ALL,
		  (N*F=comma8. osb*F=NLMNLEUR20.2 PCTN*F=pctfmt8.2 PCTSUM*osb=""*F=pctfmt8.2)
		  ALL*(N*F=comma8. osb*F=NLMNLEUR20.2 PCTN*F=pctfmt8.2 PCTSUM*osb=""*F=pctfmt8.2)
		  / BOX = "TEST";
	label bucket = "" osb = "€";
	keylabel N = "#" SUM = "" PCTN = "%#" PCTSUM = "%€" ALL = "Total";
	format bucket bucketfmt.;
	title "Test";
	title2 "the Tester";
run;

The proc format is still the same as before, since from the examples you posted it seems to be set up in the same way.

 

As the warning says, the MLF option is ignored and the ouput contains neither the subtotal rows nor the rows for which there are no values (see picture).

 

test.JPG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1201 views
  • 4 likes
  • 3 in conversation