topic Re: In proc tabulate, the spreadsheet row with the page dimension label is hidden when using the excel_XP tagset in ODS and Base Reporting

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

AdrianGriffin — Mon, 13 Apr 2015 21:34:29 GMT

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" ss:Size="9" />' NL;

put "<Interior />" Nl;

put '</Style>' nl;

set $contents_class "_contents";

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

put ' <Interior ss:Pattern="Solid" />' nl;

put ' <Protection ss:Protected="1" />' nl;

put '</Style>' nl;

set $pages_class "_pages";

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

put ' <Interior ss:Pattern="Solid" />' nl;

put ' <Protection ss:Protected="1" />' nl;

put '</Style>' nl;

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='wrap:off';

style data/font_face='Gill Sans MT' font_size=10pt verticalalign=bottom tagattr='wrap:off';

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;

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

Reeza — Tue, 14 Apr 2015 00:24:51 GMT

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?

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

AdrianGriffin — Tue, 14 Apr 2015 16:51:58 GMT

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

I'm on Excel 2010.

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

Cynthia_sas — Tue, 14 Apr 2015 17:04:58 GMT

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

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

AdrianGriffin — Tue, 14 Apr 2015 17:37:49 GMT

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" ss:Size="9" />' NL;

put "<Interior />" Nl;

put '</Style>' nl;

set $contents_class "_contents";

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

put ' <Interior ss:Pattern="Solid" />' nl;

put ' <Protection ss:Protected="1" />' nl;

put '</Style>' nl;

set $pages_class "_pages";

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

put ' <Interior ss:Pattern="Solid" />' nl;

put ' <Protection ss:Protected="1" />' nl;

put '</Style>' nl;

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='wrap:off';

style data/font_face='Gill Sans MT' font_size=10pt verticalalign=bottom tagattr='wrap:off';

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;

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

Cynthia_sas — Thu, 16 Apr 2015 19:25:46 GMT

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 XML file in SAS® 9.3 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.

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;

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

AdrianGriffin — Thu, 16 Apr 2015 20:54:25 GMT

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" ss:Size="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.

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

Cynthia_sas — Thu, 16 Apr 2015 23:47:02 GMT

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

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

Cynthia_sas — Fri, 17 Apr 2015 13:29:56 GMT

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 worksheet -- 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

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

AdrianGriffin — Fri, 17 Apr 2015 23:00:53 GMT

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

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

AdrianGriffin — Fri, 17 Apr 2015 23:02:59 GMT

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

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