Spanning headers in PROC TABULATE

PeteLund — Fri, 01 Apr 2022 19:39:58 GMT

I have a request from a customer to produce a report that has two row variables and one column variable and they want the value of the first row variable to span all the columns of the report, rather than be in the first row. They would prefer using TABULATE, but I don't know if it's possible. This little example demonstrates what they want.

proc tabulate data=sashelp.shoes;
where region in ('Asia', 'Canada') and product in ('Boot','Slipper') and Subsidiary in('Montreal','Toronto','Seoul','Bangkok');
class product region Subsidiary;
var sales;
table region=''*(subsidiary='' all='Total'),(product='' all='Total')*(sales=''*sum=''*format=comma8.);
run;

This produces this result:

What they would like is for the first column to go away and have the value span the rest of the columns, producing something like this instead:

Re: Spanning headers in PROC TABULATE

sbxkoenk — Fri, 01 Apr 2022 20:00:34 GMT

Would the customer accept a PROC REPORT solution instead of a PROC TABULATE solution?
In the former, that can be done easily!

Koen

Re: Spanning headers in PROC TABULATE

PeteLund — Fri, 01 Apr 2022 20:22:39 GMT

I've mentioned that to them already, along with the data step report writing interface. I will probably end up going with one of those solutions. But, it's been 15+ years since I did anything with TABULATE and didn't want to give up without checking for anyone having a creative solution.

Re: Spanning headers in PROC TABULATE

ballardw — Sat, 02 Apr 2022 14:57:16 GMT

The row heading part can be done with a Multilabel format and suppressing the default heading using Variablename=' ' in the table. The tricky part with Tabulate would suppressing all the statistics on the top row of each group if that is actually desired.

I am including an example of multilabel formats and code as a an example. The specific things the provided code were intended to demonstrate was indentation but that is easy to remove if desired. There details between the definition of the format and the options in Tabulate to display though. The code provides a small data set play well.

/* 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. */ 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;

Re: Spanning headers in PROC TABULATE

Ksharp — Sat, 02 Apr 2022 11:42:45 GMT

proc report data=sashelp.shoes nowd;
where region in ('Asia', 'Canada') and product in ('Boot','Slipper') and Subsidiary in('Montreal','Toronto','Seoul','Bangkok');
columns  region Subsidiary   sales,product sales=s;
define  region/group noprint;
define Subsidiary /group ' ' ;
define product/across ' ';
define sales/analysis sum ' ' format=comma32.;
define s/analysis sum 'Total'  format=comma32.;
compute after region;
Subsidiary='Total';
endcomp;
compute before region/style=header{just=l};
line region $80.;
endcomp; 
break after region/summarize  ;
run;

topic Spanning headers in PROC TABULATE in SAS Procedures

Spanning headers in PROC TABULATE

Re: Spanning headers in PROC TABULATE

Re: Spanning headers in PROC TABULATE

Re: Spanning headers in PROC TABULATE

Re: Spanning headers in PROC TABULATE