The SAS Output Delivery System and reporting techniques

In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Colleagues:

Also, it's ignoring styles set in my style template. SAS note 5076 implies that this was fixed with the v1.130, 08/02/2013 version of the tagset. Am I doing anything wrong?

--Adrianate

/*

I'm using the  Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 08/02/2013);

SAS note 5076 seems to say that this tagset fixes it;

NOTE: SAS (r) Proprietary Software 9.3 (TS1M2) Licensed to CA DEPARTMENT OF TECHNOLOGY, Site 70082650.

NOTE: This session is executing on the X64_7PRO  platform.

*/

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*This is the tagset. Based on the ExcelXP tagset, but with a mod (by Cynthia) to preserve the grid lines ;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

proc template;

   define tagset tagsets.excelxp_modxx;

      parent=tagsets.excelxp;

      define event sub_body;

         break /if $sub_body;

            set $sub_body "True";

            set $body_class "_body";

            put '<Style ss:ID="_body">' nl;

            put ' <Font ss:FontName="Gill Sans MT" ssSmiley Frustratedize="9" />' NL;

            put "<Interior />" Nl;

            put '</Style>' nl;

            set $contents_class "_contents";

            put '<Style ss:ID="_contents">' nl;

            put ' <Interior ssSmiley Tongueattern="Solid" />' nl;

            put ' <Protection ssSmiley Tonguerotected="1" />' nl;

            put '</Style>' nl;

            set $pages_class "_pages";

            put '<Style ss:ID="_pages">' nl;

            put ' <Interior ssSmiley Tongueattern="Solid" />' nl;

            put ' <Protection ssSmiley Tonguerotected="1" />' nl;

            put '</Style>' nl;

      end;

   end;

run;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*This is the style I'm using;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

proc template;

  define style styles.excel_xml;

    parent=styles.minimal;

      style body/background=transparent topmargin=0.2in bottommargin=0.2in leftmargin=0.2in rightmargin=0.2in;

      style systemtitle/font_face='Gill Sans MT' font_size=12pt just=center verticalalign=middle cellheight=24pt ;

      style systemfooter/font_face='Gill Sans MT' font_size=11pt just=left verticalalign=middle cellheight=21pt;

      style caption/font_face='Gill Sans MT' font_size=11pt cellheight=18pt tagattr='hidden:no';

      style beforecaption/font_face='Gill Sans MT' font_size=11pt cellheight=18pt tagattr='hidden:no'; 

      style pageno/font_face='Gill Sans MT' font_size=11pt;

      style byline/font_face='Gill Sans MT' font_size=11pt;

      style notecontent/font_face='Gill Sans MT' font_size=10pt verticalalign=middle;

      style table/font_face='Gill Sans MT' font_size=10pt;

      style header/font_face='Gill Sans MT' font_size=10pt

                   verticalalign=middle just=center  bordertopwidth=1pt borderbottomwidth=1pt;

      style rowheader/font_face='Gill Sans MT' font_size=10pt verticalalign=bottom just=left

                      borderbottomwidth=0pt bordertopwidth=0pt tagattr='wrapSmiley Surprisedff';

      style data/font_face='Gill Sans MT' font_size=10pt verticalalign=bottom tagattr='wrapSmiley Surprisedff';

      style parskip/cellheight=18pt background=transparent just=left;

  end;

run;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*Sending the proc tabulate to the xml destination ... ;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

ods listing close;

ods proctitle; ods escapechar='^'; ods results off;

ods tagsets.excelxp_modxx style=excel_xml file="&desktop.test.xml"

   options (sheet_interval='bygroup' sheet_label=' ' embedded_titles='yes' embedded_footnotes='yes'

            suppress_bylines='yes' skip_space='0, 0, 0, 0, 0'

            row_heights='21, 0, 0, 0, 0, 12, 0' row_height_fudge='6'

            embed_titles_once='yes'  autofit_height='Yes'  width_fudge='0.45');

options pageno=1;

proc tabulate noseps data=sashelp.cars(where=(make<'C')) f=comma10. ;

  class make; class model; classlev model/style=[width=1.8 in];

  var horsepower; var weight;

  table make=' ', model=' ', sum=' '*(horsepower weight*[style={tagattr='format:##,##0'}])/rts=40;

run;

ods tagsets.excelxp_modxx close;

ods listing;


Accepted Solutions
Solution
‎04-17-2015 09:29 AM
SAS Super FREQ
Posts: 8,721

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

I checked Tech Support notes and found this note 46949 - You cannot use a style template with the ExcelXP tagset to add grid lines to the body of a w... -- when I run the code using SAS 9.4 and Excel 2013, I do get gridlines using the custom tagset template and their style template.

cynthia

View solution in original post


All Replies
Grand Advisor
Posts: 17,428

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Cythia is probably going to have to answer it, but a few questions:

1. Does this happen with the native tagsets, not the modified version above?

2. What version of Excel are you on?

3. You've specified some row_heights, what happens if you leave that option out?

Contributor
Posts: 32

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Happens just the same with the native tagset and row_heights commented out.

I'm on Excel 2010.

SAS Super FREQ
Posts: 8,721

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Hi:

  I'm not sure I have an answer, so much as some observations and questions. The suboption for Sheet_interval='bygroup' doesn't make sense, because there is no BY statement in the TABULATE code. The PAGE dimension is NOT the same as BY group processing. If the OP had used a BY statement, then it would have been possible to make the sheet_name for every sheet the same as every BY variable value using sub-options. So I would be surprised if anything works the way the OP wants it to work.

  Where is the BY statement? Options like NOSEPS and RTS are Listing only options, so I'm not sure why they are in the TABULATE code. Before I jump into using a custom TAGSET *and* a custom STYLE template I like to ask myself how things work by default.

cynthia

Contributor
Posts: 32

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Here's an improved version of the code with sheet_interval='proc' and the listing only options taken out. The results are the same.

/*

I'm using the  Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 08/02/2013);

*SAS note 5076 seems to say that this tagset fixes it\;

NOTE: SAS (r) Proprietary Software 9.3 (TS1M2) Licensed to CA DEPARTMENT OF TECHNOLOGY, Site 70082650.

NOTE: This session is executing on the X64_7PRO  platform.

*/

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*This is the tagset. Based on the ExcelXP tagset, but with a mod (by Cynthia) to preserve the grid lines ;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

proc template;

   define tagset tagsets.excelxp_modxx;

      parent=tagsets.excelxp;

      define event sub_body;

         break /if $sub_body;

            set $sub_body "True";

            set $body_class "_body";

            put '<Style ss:ID="_body">' nl;

            put ' <Font ss:FontName="Gill Sans MT" ssSmiley Frustratedize="9" />' NL;

            put "<Interior />" Nl;

            put '</Style>' nl;

            set $contents_class "_contents";

            put '<Style ss:ID="_contents">' nl;

            put ' <Interior ssSmiley Tongueattern="Solid" />' nl;

            put ' <Protection ssSmiley Tonguerotected="1" />' nl;

            put '</Style>' nl;

            set $pages_class "_pages";

            put '<Style ss:ID="_pages">' nl;

            put ' <Interior ssSmiley Tongueattern="Solid" />' nl;

            put ' <Protection ssSmiley Tonguerotected="1" />' nl;

            put '</Style>' nl;

      end;

   end;

run;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*This is the style I'm using;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

proc template;

  define style styles.excel_xml;

    parent=styles.minimal;

      style body/background=transparent topmargin=0.2in bottommargin=0.2in leftmargin=0.2in rightmargin=0.2in;

      style systemtitle/font_face='Gill Sans MT' font_size=12pt just=center verticalalign=middle cellheight=24pt ;

      style systemfooter/font_face='Gill Sans MT' font_size=11pt just=left verticalalign=middle cellheight=21pt;

      style caption/font_face='Gill Sans MT' font_size=11pt cellheight=18pt tagattr='hidden:no';

      style beforecaption/font_face='Gill Sans MT' font_size=11pt cellheight=18pt tagattr='hidden:no'; 

      style pageno/font_face='Gill Sans MT' font_size=11pt;

      style byline/font_face='Gill Sans MT' font_size=11pt;

      style notecontent/font_face='Gill Sans MT' font_size=10pt verticalalign=middle;

      style table/font_face='Gill Sans MT' font_size=10pt;

      style header/font_face='Gill Sans MT' font_size=10pt

                   verticalalign=middle just=center  bordertopwidth=1pt borderbottomwidth=1pt;

      style rowheader/font_face='Gill Sans MT' font_size=10pt verticalalign=bottom just=left

                      borderbottomwidth=0pt bordertopwidth=0pt tagattr='wrapSmiley Surprisedff';

      style data/font_face='Gill Sans MT' font_size=10pt verticalalign=bottom tagattr='wrapSmiley Surprisedff';

      style parskip/cellheight=18pt background=transparent just=left;

  end;

run;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*Sending the proc tabulate to the xml destination ... ;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

ods listing close;

ods proctitle; ods escapechar='^'; ods results off;

ods tagsets.excelxp_modxx style=excel_xml file="&desktop.test.xml"

   options (sheet_interval='proc' sheet_label=' ' embedded_titles='yes' embedded_footnotes='yes'

            suppress_bylines='yes' skip_space='0, 0, 0, 0, 0'

          /*  row_heights='21, 0, 0, 0, 0, 12, 0' */ row_height_fudge='6'

            embed_titles_once='yes'  autofit_height='Yes'  width_fudge='0.45');

options pageno=1;

proc tabulate data=sashelp.cars(where=(make<'C')) f=comma10. ;

  class make; class model; classlev model/style=[width=1.8 in];

  var horsepower; var weight;

  table make=' ', model=' ', sum=' '*(horsepower weight*[style={tagattr='format:##,##0'}]);

run;

ods tagsets.excelxp_modxx close;

ods listing;

SAS Super FREQ
Posts: 8,721

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Hi:

  Well, you might want to work with Tech Support on this one. Depending on your version of SAS, this Tech Support note 45306 - Specifying a page dimension with PROC TABULATE and the ExcelXP tagset generates an invalid X... indicates that there might have been a problem with the PAGE dimension and TAGSETS.EXCELXP. When I run a simplified version of your code, this is what I see. And, I show PROC REPORT as an alternative too, along with PROC TABULATE and BY instead of PAGE. With the simplified syntax, running SAS 9.4 and Excel 2013, this is what I see for the code below.

compare_techniques_tab_and_report.png

cynthia

Here's the code I ran to get the above. Notice that I did NOT use a custom style or a custom tagset template, but took those defaults.

ods listing close;
ods noproctitle; 
** make some data;

proc sort data=sashelp.cars out=cars;
by make model;
where make in ('Acura', 'Buick', 'Volvo');
run;
   
** 2a) use page dimension;
ods tagsets.excelxp  style=htmlblue file="c:\temp\test_tab2a.xml"
   options(doc='Help' embedded_titles='yes' sheet_interval='proc');
   
title '2a) Defaults with PAGE but without box=page';
  
proc tabulate data= cars f=comma10. ;

  class make model; 
  classlev model / style={width=2in};
  var horsepower  weight;

  table make ,
        model=' ' ,
        sum=' '*(horsepower weight*[style={tagattr='format:##,##0'}]) /  ;
run;
ods tagsets.excelxp close;

    

**2b) use page dimension with box=_page_;
ods tagsets.excelxp  style=htmlblue file="c:\temp\test_tab2b.xml"
   options( embedded_titles='yes' sheet_interval='proc');
title '2b) Try Tabulate with BOX=_PAGE_';
  
proc tabulate data= cars f=comma10. ;

  class make model; 
  classlev model / style={width=2in};
  var horsepower  weight;

  table make ,
        model=' ' ,
        sum=' '*(horsepower weight*[style={tagattr='format:##,##0'}]) /box=_page_ ;

run;
ods tagsets.excelxp close;

     

**2c) use BY instead of page dimension;
options byline nocenter;
ods tagsets.excelxp  style=htmlblue file="c:\temp\test_tab2c.xml"
   options(embedded_titles='Yes'  sheet_interval='proc' );

title '2c) Try Tabulate with BY and all defaults';
  
proc tabulate data= cars f=comma10. ;
by make;

  class make model; 
  classlev model / style={width=2in};
  var horsepower  weight;

  table model=' ',
        sum=' '*(horsepower weight*[style={tagattr='format:##,##0'}])  ;

run;
ods tagsets.excelxp close;
        
**2d) show PROC REPORT approach;
ods tagsets.excelxp  style=htmlblue file="c:\temp\test_tab2d.xml"
   options(embedded_titles='yes' sheet_interval='proc');
title '2d) Try PROC REPORT ';
  
proc report data= cars nowd ;
  column make model horsepower weight;
  define make / group noprint;
  define model / group style(column)=Header{width=2in};
  define horsepower /sum style(column)={tagattr='format:##,##0'};
  define weight / sum style(column)={tagattr='format:##,##0'};
  break after make / page;
  compute before _page_ / style=Header{textalign=left};
    line 'Make: ' make $20.;
  endcomp;
run;
ods tagsets.excelxp close;


title;
ods listing;

Contributor
Posts: 32

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Ok, I tried your code and the '2a use page dimension' version showed the make label just as it should. Then I worked my way back to my code. The custom style and the modified tagset weren't the issue. The culprit was the skip_space='0, 0, 0, 0, 0'  in the ods options. When I added this, the line with the page dimension was hidden.

Also, the sheet_interval='proc' makes it ignore the embed_titles_once='yes' option. With sheet_interval='bygroup', the titles are included only once. This doesn't seem to do any harm, even though there is no by statement in the proc.

The only remaining issue is that the xml is ignoring style settings--both in class and classlev in the proc and in beforecaption in the custom style. It seems to be getting everything from the

put ' <Font ss:FontName="Gill Sans MT" ssSmiley Frustratedize="9" />' NL line in the tagset definition.

But I like the custom tagset that Cynthia did a couple of years back. The resulting spreadsheet is easier to navigate if the gridlines are visible.

SAS Super FREQ
Posts: 8,721

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Hi:

  When you use the Embed_titles_once='yes' I think you only see the title in Print Preview or when you print. That's where the title showed for me. That is a question for Tech Support I think. I stopped doing custom tagsets for ExcelXP because they frequently broke with each version of Excel I got.

cynthia

Solution
‎04-17-2015 09:29 AM
SAS Super FREQ
Posts: 8,721

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

I checked Tech Support notes and found this note 46949 - You cannot use a style template with the ExcelXP tagset to add grid lines to the body of a w... -- when I run the code using SAS 9.4 and Excel 2013, I do get gridlines using the custom tagset template and their style template.

cynthia

Contributor
Posts: 32

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Yes, I'm using this custom tagset, and it works fine.

BTW, thanks for your help, I think we can wrap this one up.

Contributor
Posts: 32

Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset

Embed_titles_once gives me the titles in the spreadsheet, but only before the first 'page'. So this works as advertised.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1039 views
  • 6 likes
  • 3 in conversation