Formatting titles in PROC TABULATE using ODS Excel

[Walternate]

Hi,

I am using ODS Excel to output a number of PROC TABULATE results to an Excel file. Multiple results go on the same sheet, so the issue I'm having is that the title of each output spans the maximum number of columns (ie, if one output is 5 columns wide, all titles are spanned across 5 columns even if the output actually associated with the topic is not that wide). The result is that the titles go way farther across the page than the output they are associated with. I'm hoping there's a way to make it so each title spans only the number of columns that are actually in the associated output. 

This is my approach:

ods excel file = "mypath\myfile.xlsx";

%macro output_all;
    %do i = 1 %to 3;

              %let text = %scan(&list., &i);

                       ods excel options (embedded_titles = 'yes'
                                                           sheet_name = "&text"
                                                         sheet_interval = "none"
                                          absolute_column_width = "33, 24");

                             proc tabulate data = mydata format = 12.0 style={font=("Calibri",11pt, Normal) just=c vjust=c};
                                     class &groupvar;
                                     classlev &groupvar / style=[background=silver foreground=black just=r];
                                     keylabel sum = " ";
                                     keyword all / style={background=silver foreground=black just=r};
                                     var &vlist. / style=[background=silver foreground=black];
                                     table &groupvar. = ' '*f=&groupfmt. all = 'Total', (&vlistl.) *(sum) ; 
                                     title justify=center bold font='Calibri' height=11pt color=white bcolor=firebrick &title.;
                            run;

%mend;

     %output_all;

This is what the output looks like

SHEET1

THIS IS THE FIRST OUTPUT WITH A VERY LONG TITLE THAT GOES PAST THE OUTPUT

var1 var2 var3

   5     10    15

THIS IS THE SECOND OUTPUT WITH A VERY LONG TITLE

var1 var2 var3

20     25     30 

ETC.

This is what I want it to look like, ie, I want the title centered over the output and only including the columns that have values in them for that output:

THIS IS THE FIRST

OUTPUT WITH A

VERY LONG TITLE

THAT GOES PAST

THE OUTPUT

var1 var2 var3

   5     10    15

Any help is much appreciated.

[Cynthia_sas]

Hi:
Where is your ODS EXCEL CLOSE statement? Also, can you mock up an example using SASHELP data, it's hard to make any constructive suggestions without any working code that can be tested (your code won't work with a CLOSE). Also, the Macro program can be defined OUTSIDE of the ODS EXCEL statements and just invoked INSIDE the statements. But without any data, nobody can run your code.

The only suggestion I have is that you look at the doc https://go.documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docse... for the suboptions that impact the title, such as:
TITLE_FOOTNOTE_WIDTH and TITLE_FOOTNOTE_NOBREAK.

Hope this helps,
Cynthia

[Walternate]

Hi Cynthia,

I do have an ODS Excel close statement in the program; I just forgot to include it in my code snippet. 

Here is a simplified example that nevertheless demonstrates the issue using sashelp.class:

data test1;
    set sashelp.class (keep = sex age height weight);
run;

ods excel file= "&path\test.xlsx";

%macro output_all;
    %do i = 1 %to 3;

        ods excel options (embedded_titles = 'yes'
                                            sheet_name = "test1"
                                         sheet_interval = "none");

                        %do j = 1 %to 3;
                                %let varlist = sex age height;
                                %let groupvar = %scan(&varlist, &j);
                                %if            &j = 1 %then %let vlist = age height weight;
                                %else %if &j = 2 %then %let vlist = height weight;
                                %else %if &j = 3 %then %let vlist = weight;

                                         proc tabulate data = test1 missing;
                                              class &groupvar.;
                                              keylabel sum = " ";
                                               var &vlist;
                                              table &groupvar. = ' '* all = 'Total', (&vlist.) *(sum) ;
                                              title "Really long spanning title that is going to be longer than output tables goes here";
                                         run;
                    %end;

       %end;
%mend;
    %output_all;

ods excel close;

[Cynthia_sas]

Hi:
Did you look you look at the documentation site: https://go.documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docse... specifically for the suboptions that impact the title, such as:
TITLE_FOOTNOTE_WIDTH and TITLE_FOOTNOTE_NOBREAK. I think they might help you.

Cynthia

[Walternate]

I actually tried inserting 

ods options (title_footnote_width = &numcol); 

In the macro before each proc tabulate, with an automatically generated macro variable numcol which was the number of columns of each specific output. What SAS did was take only the first value of &numcol and apply that to all the outputs (instead of letting it change with each output). 

Neither value of TITLE_FOOTNOTE_NOBREAK did anything to fix the problem. 

[Cynthia_sas]

Hi, when I reduce your code to a simpler example, for just 1 sheet, with 3 outputs and varying the TITLE_FOOTNOTE_WIDTH option, I do see that only the first width is used. I believe that is a function of how SHEET_INTERVAL="NONE" works.

Here's the simplified code I tried

data test1;
    set sashelp.class (keep = sex age height weight);
run;

ods excel file="c:\temp\test_title.xlsx";
   
 ods excel options (embedded_titles = 'yes'
                    sheet_name = "test1"
                    sheet_interval = "none"
					title_footnote_width = "5");
 
        proc tabulate data = test1 missing;
             class sex;
             keylabel sum = " ";
              var age height weight;
             table sex = ' '* all = 'Total', 
                   (age height weight) *(sum) ;
             title "1) Really long spanning title that is going to be longer than output tables goes here";
        run;
   
 ods excel options (embedded_titles = 'yes'
					title_footnote_width = "4");
 
        proc tabulate data = test1 missing;
             class age;
             keylabel sum = " ";
              var height weight;
             table age = ' '* all = 'Total', 
                   (height weight) *(sum) ;
             title "2) Really long spanning title that is going to be longer than output tables goes here";
        run;
   
 ods excel options (embedded_titles = 'yes'
					title_footnote_width = "3");
 
        proc tabulate data = test1 missing;
             class height;
             keylabel sum = " ";
              var weight;
             table height = ' '* all = 'Total', 
                   (weight) *(sum) ;
             title "3) Really long spanning title that is going to be longer than output tables goes here";
        run;   
 
ods excel close;

and, here you can see that both #1 and #2 use 5 columns (I used 5 as the number of columns to span, instead of 4 because the TOTAL column also needs to be counted here.

However, if you want to check with Tech Support, you might see whether they have a workaround for you or they can verify that this is a side effect of using SHEET_INTERVAL="NONE".

  I do note that when I put each TABULATE on a separate sheet, the suboption for TITLE_FOOTNOTE_WIDTH does work as it should. (TABULATE code was the same for the screen shot below -- only ODS statements changed)

Hope this helps,

Cynthia