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

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;

   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;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

10 REPLIES 10
Reeza
Super User

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?

AdrianGriffin
Obsidian | Level 7

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

I'm on Excel 2010.

Cynthia_sas
SAS Super FREQ

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

AdrianGriffin
Obsidian | Level 7

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;

   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;

Cynthia_sas
SAS Super FREQ

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;

AdrianGriffin
Obsidian | Level 7

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.

Cynthia_sas
SAS Super FREQ

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

Cynthia_sas
SAS Super FREQ

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

AdrianGriffin
Obsidian | Level 7

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

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

AdrianGriffin
Obsidian | Level 7

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

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
  • 10 replies
  • 2318 views
  • 6 likes
  • 3 in conversation