As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


ExcelXP Multiple Titles fix

From sasCommunity
Jump to: navigation, search

There are two interesting ExcelXP options

  embedded_titles='yes'
  embed_title_once='yes'

The problem with these settings is that out of the box from SAS they only work when the issue of titles being repeated is during by group processing, when all by groups are sent to a single worksheet (The default is one sheet per by group)

The intuitive expectation is that there will be only one title regardless of how output gets placed in the worksheet. In my case I had a very large Proc TABULATE step with several TABLE statements. The title was appearing above each table and that is not what I wanted or expected when using the above option settings.

What's the fix ? Update Tagsets.ExcelXP and use the updated version. Fortunately the tagset source code is available for examination. Unfortunately it is not documented and it is written in Proc TEMPLATE TAGSET language.

This code makes very minor changes to two of the tagset event handlers. Unfortunately those handlers have quite a bit of code and need to be fully copied in order to tweak and override the parent, regardless, here it is. Test code is at the very bottom.

  proc template;
     define tagset Tagsets.ExcelXP_RAD;
        parent = Tagsets.ExcelXP;
  
        define event worksheet;
           start:
              do /if ^$doing_blank;
                 trigger do_blank_worksheet /if $blank_sheet_name;
              done;
  
              trigger get_global_margins;
  
              break /if $worksheet_started;
  
              unset $cellwidths;
              unset $worksheet_widths;
              unset $worksheet_has_panes;
              unset $worksheet_has_autofilter;
              unset $worksheet_has_titles;
              unset $worksheet_has_footers;
              unset $possible_row_repeat_start;
              unset $possible_row_repeat_end;
              unset $possible_col_repeat_start;
              unset $possible_col_repeat_end;
  
              do /if ^$worksheet_row;
                 eval $worksheet_row 0;
              done;
  
              eval $numberOfWorksheets $numberOfWorksheets +1;
  
  eval $rad_embedded_title_count 0;
  
              trigger worksheet_label;
  
              trigger clean_worksheet_label;
  
              trigger contents_entry /if ^cmp( event_name, "byline");
  
              do /if cmp( $worksheetname, " ");
                 eval $numberOfWorksheets $numberOfWorksheets -1;
              done;
  
              do /if $debug_level = -8;
                 putlog "!!!!!!!!" Event_name "  Worksheet: |" $worksheetname "|  " $numberOfWorksheets;
              done;
  
              unset $$worksheet_start;
  
              open worksheet_start;
              put "<x:WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">" NL;
  
              do /if $fittopage;
                 put "<FitToPage />" NL;
              done;
  
              put "<Print>" NL;
              put "<ValidPrinterInfo/>" NL;
  
              trigger do_paperSize;
  
              do /if $scale;
                 put "<Scale>";
                 put $scale;
                 put "</Scale>" NL;
              done;
  
              do /if $pages_fitwidth;
                 put "<FitWidth>" $pages_fitwidth "</FitWidth>" NL;
              done;
  
              do /if $pages_fitheight;
                 put "<FitHeight>" $pages_fitheight "</FitHeight>" NL;
              done;
  
              put "<LeftToRight/>" NL /if $left_to_right;
              put "<HorizontalResolution>";
              put $print_dpi;
              put "</HorizontalResolution>" NL;
              put "<VerticalResolution>";
              put $print_dpi;
              put "</VerticalResolution>" NL;
              put "<Gridlines/>" NL /if $gridlines;
              put "<BlackAndWhite/>" NL /if $blackandwhite;
              put "<DraftQuality/>" NL /if $draftquality;
              put "<RowColHeadings/>" NL /if $RowColHeadings;
              put "</Print>" NL;
              put "<Zoom>" $Zoom "</Zoom>" NL /if $Zoom;
              put "<PageLayoutZoom>" $PageLayoutZoom "</PageLayoutZoom>" NL /if $PageLayoutZoom;
              put "<x:PageSetup>" NL;
              put $$page_setup;
  
              do /if ($embedded_titles &  ^ $system_title_setup) |  ^ $system_title_setup;
                 do /if $print_header &  ^ $xheader;
                    putq "<x:Header x:Data=" $print_header;
                    putq " x:Margin=" $print_header_margin;
                    put "/>" NL;
                    unset $xheader;
                 done;
              done;
  
              unset $system_title_setup;
  
              do /if ($embedded_footnotes &  ^ $system_footer_setup) |  ^ $system_footer_setup;
                 do /if $print_footer &  ^ $xfooter;
                    putq "<x:Footer x:Data=" $print_footer;
                    putq " x:Margin=" $print_footer_margin;
                    put "/>" NL;
                    unset $xfooter;
                 done;
  
              done;
  
              unset $system_footer_setup;
  
              do /if any( $landscape, $center_horizontal, $center_Vertical);
                 put "<Layout";
                 put " x:Orientation=""Landscape""" /if $landscape;
                 put " x:CenterHorizontal=""1""" /if $center_horizontal;
                 put " x:CenterVertical=""1""" /if $center_vertical;
                 put "/>" NL;
              done;
  
              do /if any( $marginbottom, $marginleft, $marginright, $margintop);
                 put "<PageMargins";
                 putq " x:Bottom=" $marginbottom;
                 putq " x:Left=" $marginleft;
                 putq " x:Right=" $marginright;
                 putq " x:Top=" $margintop;
                 put "/>" NL;
              done;
  
              put "</x:PageSetup>" NL;
              close;
  
              open worksheet;
              set $worksheet_started "True";
  
           finish:
              break /if ^$worksheet_started;
              unset $worksheet_started;
  
              do /if ^$$worksheet;
                 do /if ^$doing_blank;
                    do /if ^cmp( $worksheetname, " ");
                       eval $numberOfWorksheets $numberOfWorksheets -1;
                    done;
                    break;
                 done;
              done;
  
              trigger write_contents_entries;
  
              trigger worksheet_tab;
              unset $tabname_is_done;
  
              open master_worksheet;
              putq "<Worksheet ss:Name=" $worksheetName ">" NL;
  
              trigger print_repeats;
              set $current_worksheet $worksheetName;
              unset $tempWorksheetName;
              unset $worksheetName;
              put $$worksheet_start;
              unset $$worksheet_start;
  
              trigger worksheet_head_end;
  
              trigger table_start;
              put $$worksheet;
  
              trigger embedded_footnotes /if ^cmp( $sheet_interval, "none");
              putl "</Table>";
              eval $table_count 0;
              unset $$worksheet;
              unset $byGroupLabel;
              putl "</Worksheet>";
              eval $worksheet_row 0;
              unset $embedded_titles_done;
        end;
  
        define event embedded_title;
           break /if ^$embedded_titles;
           break /if ^$titles;
           break /if $embedded_titles_done;
  
           do /if cmp( $sheet_interval, "bygroup");
  
              do /if $one_embedded_title_set;
                 unset $titles /breakif $worksheet_has_titles;
                 set $worksheet_has_titles "True";
              done;
  
           done;
  
  do /if $one_embedded_title_set and $rad_embedded_title_count > 0;
     unset $titles;
     break;
  done;
  
           do /if ^$worksheet_row;
              eval $worksheet_row 0;
           done;
  
           eval $count 1;
  
           do /while $count <= $titles;
              eval $worksheet_row $worksheet_row +1;
              set $url $title_urls[$count ];
              set $span_cell_style $title_styles[$count ];
              set $orig_span_style $o_title_styles[$count ];
  
              do /if ^cmp( $title_heights[$count], " ");
                 set $height $title_heights[$count ];
  
              else;
                 set $height $row_heights["Title" ];
              done;
  
              unset $merge;
              set $merge "True" /if ^contains( $span_cell_style, "__l");
              set $span_cell_value $titles[$count ];
  
              trigger span_cell start;
  
              trigger span_cell finish;
              eval $count $count +1;
              unset $url;
           done;
  
           unset $span_cell_style;
           set $embedded_titles_done "True";
           unset $title_urls;
           unset $height;
           set $skip_multiplier $skip_factor["Title" ];
  
           trigger parskip;
  
  eval $rad_embedded_title_count $rad_embedded_title_count +1;
        end;
  
        log_note = 'ExcelXP fixes by RAD';
     end;
  run;

Test code

  options nocenter nonumber nodate ls=max ps=max;
  
  ods path reset;
  
  ods tagsets.ExcelXP_RAD file="%sysfunc(pathname(WORK))\sample%sysfunc(monotonic()).xml" style=plateau 
    options
  ( 
  embedded_titles='yes' 
  embed_titles_once='yes'
  wraptext='yes'
  sheet_interval='none' 
  sheet_name='One'
  );
  
  title1 "Test for options(embedded_titles='yes' and embed_titles_once='yes')";
  title2 "This is the title for the first tab. It contains output from more than one Proc";
  title3 "The title does not appear above the second proc";
  
  proc print data=sashelp.class; where name=:'A'; 
  var name;
  var sex age /style=[width=.6in just=c];
  var height weight;
  run;
  proc print data=sashelp.class; where name=:'A'; 
  var name;
  var sex age /style=[width=.6in just=c];
  var height weight;
  run;
  
  ods tagsets.ExcelXP_RAD options(sheet_interval='none' sheet_name="Two");
  
  title1 "Test for options(embedded_titles='yes' and embed_titles_once='yes')";
  title2 "This is the title for the second tab. It contains output from more than one Proc";
  title3 "The title does not appear above the second proc";
  
  proc print data=sashelp.class; where name=:'A'; 
  var name;
  var sex age /style=[width=.6in just=c];
  var height weight;
  run;
  proc print data=sashelp.class; where name=:'A'; 
  var name;
  var sex age /style=[width=.6in just=c];
  var height weight;
  run;
  
  ods tagsets.ExcelXP_RAD options(sheet_interval='none' sheet_name="Two");
  
  title1 "TWO Again: Test for options(embedded_titles='yes' and embed_titles_once='yes')";
  title2 "This is the title for the second tab. It contains output from more than one Proc";
  title3 "The title does not appear above the second proc";
  
  proc print data=sashelp.class; where name=:'A'; 
  var name;
  var sex age /style=[width=.6in just=c];
  var height weight;
  run;
  proc print data=sashelp.class; where name=:'A'; 
  var name;
  var sex age /style=[width=.6in just=c];
  var height weight;
  run;
  
  ods _all_ close;
  
  options source;