BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sgnolek
Obsidian | Level 7

I am creating multi-tab excel spreadsheets from PROC TABULATE output and everything is working fine. However, I would really like the titles at the top of the page (which are embedded headers) to have a larger, bold font. Any changes I make to the TITLE1 statement (ex: height = 2 bold) don't seem to have an effect on the embedded header, and because I have a BY variable in my TITLE1 statement I don't think that I could switch and use a PRINT_HEADER option to generate the title instead. A section of my code is shown below. Any hints?

ods tagsets.excelxp style = journal file = "C:\xxx"

                                options (sheet_interval = 'bygroup'

                                              suppress_bylines = 'yes'

                                              embed_titles_once = 'yes'

                                              fittopage = 'yes'

                                             sheet_label = 'XYZ'

                                              printer_footer = "sysdate9 at %sysfunc(time(), time.)");

proc tabulate;

  title1 "XXX College Means Report for #byval1 Students';

... remaining tabulate statements...

run;

1 ACCEPTED SOLUTION

Accepted Solutions
sgnolek
Obsidian | Level 7

Thanks to Chevell at SAS Technical support for providing the solution below (SAS 7611038695). Adding BOTH the EMBED_TITLES_ONCE and EMBEDDED_TILES options will provide text in row 1 at the top of the excel file that can be modified.

ods tagsets.excelxp style=journal                      
file="c:\temp.xml"                  
options(sheet_interval = 'bygroup'                
             suppress_bylines = 'yes'

           embedded_titles="yes"            
           embed_titles_once = 'yes'                 
           sheet_label = "sheet name here"

           print_footer = "This is the text that appears in the footer of each sheet");

proc tabulate data=sashelp.class;

      title1 'This is the title which appears in the header of each sheet due to the EMBED_TITLES_ONCE = yes option. I want to change the format of the text for
this';

      var height weight;

      table height;

      table weight;

by age;

run;

ods tagsets.excelxp close;

View solution in original post

11 REPLIES 11
Cynthia_sas
SAS Super FREQ

Hi:

  When I use the code sample below with BY group processing, I am able to make the title red, bold and 14pt on every sheet.  See screenshot.

cynthia

ods _all_ close;

options nobyline;

   

proc sort data=sashelp.class out=class;

  by age sex;

  where age le 13;

run;

   

ods tagsets.excelxp style = journal file = "C:\temp\red_title.xml"

                    options (sheet_interval = 'bygroup'

                             suppress_bylines = 'yes'

                             embedded_titles= 'yes'

                             fittopage = 'yes'

                             sheet_label = 'XYZ'

                             printer_footer = "sysdate9 at %sysfunc(time(), time.)");

  

proc tabulate data=class;

  title1 bold h=14pt f='Courier New' color=red

         'Report for Students who are Age #byval1';

  by age;

  class sex;

  var height;

  table sex all,

        height*(min mean max);

run;

ods tagsets.excelxp close;


embedded_titles_red.png
sgnolek
Obsidian | Level 7

I see the example. If you use the option EMBED_TITLES_ONCE = 'yes' versus EMBEDDED_TITLES = 'yes' does it work (it didn't for me). I actually have multiple table statements within the PROC TABULATE so I didn't want a title before each set of output, just one at the top of the page. Using the EMBED_TITLES_ONCE = 'yes' option seems to ignore the formatting of the TITLE1 statement.

sgnolek
Obsidian | Level 7

So does anyone have an additional hints about how to format the title when using the EMBED_TITLES_ONCE option? I only want one title, at the top, and I have multiple tables statements within proc tabulate.

ballardw
Super User

Have you tried style PRETEXT? You can add this to the description for the first table

table x,

        y*n

        / style=[Pretext='Text to show before the table'];

you can add FONTSIZE= and FONTWEIGT= attributes before Pretext to modify appearance.

Of course if you are using title2 or more statments this won't quite work as pretext comes after titles.

sgnolek
Obsidian | Level 7

I doesn't appear that the Pretext Style carries through when ODS tagsets.excelxp is used. When I  try your code, but with adding the ODS statements as shown, I don't see the pretext in the resulting EXCEL file. Does it work for you?

ods tagsets.excelxp style=journal

                    file="C:\SAS\means test.xls";

table x,

        y*n

        / style=[Pretext='Text to show before the table'];

ods tagsets.excelxp close;

ballardw
Super User

Well, you need a full proc tabulate with data set class and var variables to go with the table. That was just a stub as there weren't any details from your proc tabulate statement.

And I send to xml files explicitly but it works. I'm generally doing this to provide per table "titles" within a single call to proc tabulate, as well as using POSTTEXT to provide "footnotes" for just that table.

sgnolek
Obsidian | Level 7

Sorry, I should have been more clear. I understood that, I was just using the generic tabulate statements as a placeholder. Here's what I am trying to do...

I am using one proc tabulate procedure, with a number of tables statements, and a BY variable. I want to generate a spreadsheet for each by group. For example, if I have 4 tables called A, B, C and D, and two levels of the BY variable (level1, level2) I want an excel workbook with two 'sheets' (one for level1 and one for level2), and all four tables (A, B, C, D) in each sheet. I don't want titles above each table, and I don't want to see a byline above each table.

So I am using SAS statements like this:

ods tagsets.excelxp style=journal

                    file="file location here"

                    options(sheet_interval = 'bygroup'

                    suppress_bylines = 'yes'

                    embed_titles_once = 'yes'

                     fittopage = 'yes'

                    sheet_label = "sheet name here"

                    print_footer = "This is the text that appears in the footer of each sheet");

proc tabulate;

     title1 'This is the title which appears in the header of each sheet due to the EMBED_TITLES_ONCE = yes option. I want to change the format of the text for this';

     var variableA variableB variableC variableD;

     table variableA/ bunch of options here;

     table variableB/ bunch of different options here;

     table variableC/ another bunch of options here;

     table variableD/ yet another bunch of options here;

by level;

run;

ods tagsets.excelxp close;

If I add the style and pretext statements in the first table statement, they don't appear in the EXCEL workbook (even if I don't include the tagset options shown).

Cynthia_sas
SAS Super FREQ


Hi:

  There's always more than one way to do things. This is a "brute force" example. To make it more streamlined and/or more elegant, there are other ways to write the code. You can "simulate" by group processing with separate proc tabulate steps, that changes the sheet_interval option. Then, you set the title using your regular controls and reset the title between tabulate steps.

  It is an alternative approach. PRETEXT is an alternate approach. Working with Tech Support is an alternate approach. If you are determined to use BY group processing with EMBED_TITLES_ONCE suboption, then Tech Support is your best resource. You will find out definitively, whether there is any other workaround.

  In the code below, I use the SEX variable (M or F) instead of your Level 1 and Level 2. Then, I have 2 TABULATE steps with 1 TABLE statement each, instead of 1 TABULATE with B and multiple TABLE statements each.

cynthia

title;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\e_t_o.xml'

    style=journal

    options(embedded_titles='yes'

            sheet_interval='none'

            sheet_name='Level 1');

proc tabulate data=sashelp.class;

  class age;

  var height;

  title bold c=purple h=14pt 'Female Title is Purple';

  where sex = 'F';

  table age all,

        height*(n min max mean median) / box='Height';

run;

title;

proc tabulate data=sashelp.class;

  class age;

  var weight;

  where sex = 'F';

  table age all,

        weight*(n min max mean median)/box='Weight';

run;

ods tagsets.excelxp options(embedded_titles='yes'

    sheet_interval='none' sheet_name='Level 2');

proc tabulate data=sashelp.class;

  class age;

  var height;

  title bold c=red h=14pt 'Male Title is Red';

  where sex = 'M';

  table age all,

        height*(n min max mean median) /box='Height';

run;

title;

proc tabulate data=sashelp.class;

  class age;

  var weight;

  where sex = 'M';

  table age all,

        weight*(n min max mean median)/box='Weight';

run;

ods tagsets.excelxp close;

sgnolek
Obsidian | Level 7

Thank you  for the feedback. I am using BY group processing (and EMBED_TITLES_ONCE) because I don't want to have to specify the specific  values for each level (they change from data set to data set).

I don't think that the PRETEXT function works with tagsets.excelxp. When I change the code you provided as an example, but add the PRETEXT options as shown below, I don't any errors but I also don't get the pretext. I don't know if it is something I am doing, or something that just doesn't work.

If I want to work with Tech Support, what would be the best way to do that?

title;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\e_t_o.xml'

    style=journal

    options(embedded_titles='yes'

            sheet_interval='none'

            sheet_name='Level 1');

proc tabulate data=sashelp.class;

  class age;

  var height;

  title bold c=purple h=14pt 'Female Title is Purple';

  where sex = 'F';

  table age all,

        height*(n min max mean median) / box='Height' style=[Pretext='Text to show before the table'];

run;

title;

proc tabulate data=sashelp.class;

  class age;

  var weight;

  where sex = 'F';

  table age all,

        weight*(n min max mean median)/box='Weight';

run;

ods tagsets.excelxp options(embedded_titles='yes'

    sheet_interval='none' sheet_name='Level 2');

proc tabulate data=sashelp.class;

  class age;

  var height;

  title bold c=red h=14pt 'Male Title is Red';

  where sex = 'M';

  table age all,

        height*(n min max mean median) /box='Height';

run;

title;

proc tabulate data=sashelp.class;

  class age;

  var weight;

  where sex = 'M';

  table age all,

        weight*(n min max mean median)/box='Weight';

run;

ods tagsets.excelxp close;

Cynthia_sas
SAS Super FREQ

Hi:

  At the bottom of every page in the forum, is a gray area, with the words "submit a problem" highlighted in blue. Those words are a hyperlink and you can click them to go to the page where you can open a track with Tech Support. They will need your site license number. Also useful information for them will be your version of SAS, your operating system and an example of the code and data you are working with. Since the code you've posted here uses SASHELP.CLASS, that makes it very easy for them to grab the code and run it. It would probably be useful if you included a link to this set of postings so they can see what you've tried and what advice you've already gotten.

  BTW -- I said that my method was the "brute force" method. There are ways to "macro-ize" the code so you would not need to know the BY values ahead of time and manually code them -- you could have a SAS macro program detect the by values and invoke the macro as needed. However, a discussion of SAS macros on top of this discussion is probably out of scope.

  I didn't ask this before, but since you are using PRINT_FOOTER, have you thought about the PRINT_HEADER suboption, if you can't get EMBED_TITLES_ONCE to work?

cynthia

sgnolek
Obsidian | Level 7

Thanks to Chevell at SAS Technical support for providing the solution below (SAS 7611038695). Adding BOTH the EMBED_TITLES_ONCE and EMBEDDED_TILES options will provide text in row 1 at the top of the excel file that can be modified.

ods tagsets.excelxp style=journal                      
file="c:\temp.xml"                  
options(sheet_interval = 'bygroup'                
             suppress_bylines = 'yes'

           embedded_titles="yes"            
           embed_titles_once = 'yes'                 
           sheet_label = "sheet name here"

           print_footer = "This is the text that appears in the footer of each sheet");

proc tabulate data=sashelp.class;

      title1 'This is the title which appears in the header of each sheet due to the EMBED_TITLES_ONCE = yes option. I want to change the format of the text for
this';

      var height weight;

      table height;

      table weight;

by age;

run;

ods tagsets.excelxp close;

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!

How to Concatenate Values

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.

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
  • 11 replies
  • 14550 views
  • 1 like
  • 3 in conversation