The SAS Output Delivery System and reporting techniques

excelxp tagsets - two questions

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

excelxp tagsets - two questions

I am trying to create an excelfile, which is quite huge. I am using severeral proc tabulates, but want one excelsheet. This works in principle, but the second and later proc tabulates are created below the first one. I want them right from the first one.

Is this possible?

I use a lot of picture formats to simplify/and or  replace calculations. Now there seems to be no way to show these numbers correctly in excel. They are fine in SAS and in the XML-file, but excel mixes . and , as it likes (so it seems to me). Could be some German/English number format conversion problem, but I cant find a way to get it right.

I am grateful for any idea.


Accepted Solutions
Solution
‎11-20-2011 08:53 AM
SAS Super FREQ
Posts: 8,744

Re: excelxp tagsets - two questions

Hi, Christa:

  Peter is correct, it is inappropriate to put TAGATTR onto the header which is all that is styled by the VAR statement ( you can see from my code and screenshot where the headers for HEIGHT and WEIGHT are a purple background) -- those cells contain text strings.
   
  And, I thought I explained that style(column) is NOT going to work for TABULATE. I don't have any style(column) versions of style override in my code for TABULATE. The error, expecting an =, is generally one you get when ODS can't quite interpret what you want to do with your style override. usually you have spelled an attribute wrong or used a wrong attribute. I expect you might be getting that error because ODS, in PROC TABULATE is actually expecting an = after the keyword STYLE in the statement.
           
  In order to apply a style to a particular data cell in TABULATE, you either have to put the style override on the TABULATE statement or down in the TABLE statement. But I am not sure that you even need to use TAGATTR if you want the COMMAX format. When I do this:
   

ods tagsets.excelxp file='c:\temp\commax.xml'
    style=sasweb;

proc tabulate data=sashelp.shoes;
  var sales;
  class product;
  table product all,
        sales*sum*f=commax14.2;
run;
ods _all_ close;

 

Then this is what TAGSETS.EXCELXP sends to Excel (shown for 1 number cell):
<Data ss:Type="String">7.933.707,00</Data>

So I would recommend trying the simple SAS format first. If you need more help with TAGATTR or your EXCELXP code beyond this point, you might want to work with Tech Support.

cynthia

View solution in original post


All Replies
Valued Guide
Posts: 2,175

excelxp tagsets - two questions

To have just one excel workbook, do not close the ODS destination until after the last PROC TABULATE

Using tagattr, the tagsets.excelxp destination allows you to use a custom excel format.

You will need to translate a SAS picture format into an excel custon format.

SAS Super FREQ
Posts: 8,744

excelxp tagsets - two questions

Hi:

  In this paper,

http://support.sas.com/resources/papers/proceedings11/266-2011.pdf I have examples of using the TAGATTR style attribute with Microsoft formats (including examples of building a custom format).

I'm not exactly sure what you mean when you say that you want the second and later tabulates to be "right from the first one" do mean "right next to" horizontally? Or do you mean something else. If you turn off the sheet_interval sub-option, then the second and later tabulates will be placed underneath the first one. For some concrete examples of using the sheet_interval suboption, there is a user group presentation that was given last year called "Under the Hood: How to Use the ExcelXP Tagset" and the programs that went with that talk are here: http://support.sas.com/rnd/papers/2010/under_hood_excelxp.zip in a zip file.

cynthia

Occasional Contributor
Posts: 18

Re: excelxp tagsets - two questions

Hi Cynthia,

thank you for your answer. To clarify: I want my second tabulate right next horizontally (and dont know how to acchieve it).

Second problem:

I checked the style options, but they do not work within tabulate (?) for me, in proc report it is fine.

Christa

27 Proc Tabulate Data = fertig missing order=data ;

28 Class Jahr AGS Kla1-Kla14 Klb1-Klb11;

29 VAR f3;

30 var f4 / style(column)={tagattr='#.###.###,00'};

_

73

76

ERROR 73-322: Erwartet wird ein(e) =.

ERROR 76-322: Syntaxfehler, Anweisung wird ignoriert.

Valued Guide
Posts: 2,175

excelxp tagsets - two questions

don't think horizontally is going to work

SAS Super FREQ
Posts: 8,744

Re: excelxp tagsets - two questions

Hi:

  Peter is correct, you can get 1 table underneath another table on the same sheet, using TAGSETS.EXCELXP. But 2 tables side-by-side is NOT something that you can accomplish with TAGSETS.EXCELXP. However, you might be able to use paneling (which is how you get side-by-side tables) if you switched to the ODS TAGSETS.MSOFFICE2K_X destination. The difference is that TAGSETS.MSOFFICE2K_X creates HTML tags to perform the panelling, as shown on the information page:

http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html#paneling Creating multi-sheet workbooks is not as straightforward with HTML as it is in the ExcelXP XML-based destination, but do-able.

   

  STYLE= overrides DO work for PROC TABULATE. But, you have to use the correct syntax. PROC REPORT and PROC PRINT both require a report area in parentheses such as you show with

style(column) or style(header) -- however, TABULATE does not use this form of syntax. Every TABULATE statement only has an impact on a specific area of the output report, so the area in parentheses is not required for TABULATE. For example, the code below produces the attached (somewhat garishly colored) screenshot of the output when opened and rendered in Excel.

cynthia

ods listing close;

proc format;
  value wtfore low-70='yellow'
               71-100='cxcccccc'
               other='beige';
run;
 
ods tagsets.excelxp file='tab_style.xml'
    style=sasweb;

proc tabulate data=sashelp.class f=6.2;
  var height weight / style={foreground=white background=purple};
  class sex / style={foreground=black background=yellow};
  class age / style={foreground=black background=green};
  classlev age / style={background=cxeeeeee foreground=red};
  classlev sex / style={background=red foreground=white};
  table age all*{s={foreground=blue background=cyan font_size=12pt font_weight=bold}},
        sex*(n*f=6.) mean*(height weight*{s={background=wtfore.}})
       / style_precedence=row
         box={label='Wombat' style={background=orange foreground=black}};
  keyword mean / style={background=white foreground=purple};
  keyword all / style={background=white foreground=green font_size=12pt font_weight=bold};
  keylabel n=' ';
run;

ods tagsets.excelxp close;


tabulate_style.jpg
Valued Guide
Posts: 2,175

Re: excelxp tagsets - two questions

Hi Cynthia

I like your demo of Tabulate and excelxp, but am surprised that the background OTHER='beige' didn't seem to take effect when I tried it!

Is this an issue with the definition of that color in my excel environment, or in SAS?

regards

Peter

SAS Super FREQ
Posts: 8,744

excelxp tagsets - two questions


Hi, Peter:

  To find out whether it's your screen resolution or Excel (Excel before 2007 only had a palette of 56 colors, make the color something like cyan (which does work, right?). Beige should be translated in the SAS registry to a hex number ...what version of Excel are you running?

cynthia

Valued Guide
Posts: 2,175

Re: excelxp tagsets - two questions

thank you Cynthia

with orange instead of beige I see the effect (so something about my release of office 2007 must be blocking the beige). 

I applied the SAS registry RGB colors for beige F5,F5,DC in the "more colors" dialog in excel (as 245,245,220) and although it looked a better beige in the color chart area in the dialog box, once selected the fill of the data cell was still just "almost white"!

I shall have to review the colors my excel can deliver as fill?

In that color fill dialog, I get a satisfactory result by pulling down the slider until the blue element is 150

peter

p.s.

displayed on my system (nvidia reports 59hertz refresh, and highest colors=32M), your screeen clip provides the same "beige" as my excel!

Valued Guide
Posts: 2,175

Re: excelxp tagsets - two questions

Aaaaah !

My laptop must be the culprit! Cynthia's screen clip of excel with a beige background appears clearly different when viewed on my iPhone - very much better distinguished from white background. So, not in SAS and not in Excel, I must resolve this color problem in my laptop!

So, apologies and thanks to Cynthia

Valued Guide
Posts: 2,175

Re: excelxp tagsets - two questions

Christa

I'm not sure of the translation of your error message

ERROR 73-322: Erwartet wird ein(e) =.

ERROR 76-322: Syntaxfehler, Anweisung wird ignoriert.

but it may be that you are applying a tagattr=format in the wrong place

As Cynthia's example results show, the style definitions on a VAR statement apply to the VAR header.

A number format would apply to the statistics for a VAR, and that is taken from within the tagattr= definition, like

   tagattr="format:#.###.###"

can be defined either

  • on the PROC TABULATE statement to apply to all VAR/statistic cells, or
  • in the TABLE statement within the 'crossing definition'

As you might expect a definition in the table statement overrides a definition in the PROC statement. 

Also within the tagattr definition, you can define other things like datatype=date or datatype=url

Solution
‎11-20-2011 08:53 AM
SAS Super FREQ
Posts: 8,744

Re: excelxp tagsets - two questions

Hi, Christa:

  Peter is correct, it is inappropriate to put TAGATTR onto the header which is all that is styled by the VAR statement ( you can see from my code and screenshot where the headers for HEIGHT and WEIGHT are a purple background) -- those cells contain text strings.
   
  And, I thought I explained that style(column) is NOT going to work for TABULATE. I don't have any style(column) versions of style override in my code for TABULATE. The error, expecting an =, is generally one you get when ODS can't quite interpret what you want to do with your style override. usually you have spelled an attribute wrong or used a wrong attribute. I expect you might be getting that error because ODS, in PROC TABULATE is actually expecting an = after the keyword STYLE in the statement.
           
  In order to apply a style to a particular data cell in TABULATE, you either have to put the style override on the TABULATE statement or down in the TABLE statement. But I am not sure that you even need to use TAGATTR if you want the COMMAX format. When I do this:
   

ods tagsets.excelxp file='c:\temp\commax.xml'
    style=sasweb;

proc tabulate data=sashelp.shoes;
  var sales;
  class product;
  table product all,
        sales*sum*f=commax14.2;
run;
ods _all_ close;

 

Then this is what TAGSETS.EXCELXP sends to Excel (shown for 1 number cell):
<Data ss:Type="String">7.933.707,00</Data>

So I would recommend trying the simple SAS format first. If you need more help with TAGATTR or your EXCELXP code beyond this point, you might want to work with Tech Support.

cynthia

Occasional Contributor
Posts: 18

Re: excelxp tagsets - two questions

Hi, Cynthia,

thank you so much for your helpful answer. I am using a simpler picture format now, which works for me beautifully. Just one more question: Is there a list somewhere what formatting can be done in excel and how to write this in SAS ODS (center vertically, italic, ....)?

Christa

SAS Super FREQ
Posts: 8,744

Re: excelxp tagsets - two questions

Hi, Christa:

  In our reporting class, we teach how to use STYLE= overrides with PROC REPORT and PROC TABULATE. How each destination interprets those style overrides is up to the destination. For example, border lines look slightly different, thinner in RTF versus HTML. Each destination has its own way of dealing with style information (and format information) from SAS. Most of the destinations, "play nice" with the style and format information from SAS. Excel is notorious for ignoring information from SAS, such as column width, leading zero, number of decimal places, whether a column is a string, etc, etc. So while you can find out how to use STYLE overrides with PROC REPORT and PROC TABULATE, you have to test your overrides in every destination that will be opening your file.

  For example, when you use ODS, you can create either HTML or XML files that Excel knows how to open and render. So, if you run this code, and then, compare the 2 HTML files opened in a browser versus opened in Excel. (You will have to create them in SAS and then do a File --> Open from inside Excel). With one file (the Microsoft HTML open_mso_w_excel.html), Excel respects the overall color scheme; with the other file (HTML4 open_ht4_w_excel.html), Excel ignores the overall color scheme. Both files will look the same in Internet Explorer. The files will look different in Excel. Both files are HTML files. One "flavor" of HTML is Microsoft-friendly; the other "flavor" of HTML is the industry standard HTML. The XML file can also be opened in Excel and it will respect the overall color scheme because it is Microsoft "flavor" of XML.

ods html file='c:\temp\open_ht4_w_excel.html' style=sasweb;

ods msoffice2k file='c:\temp\open_mso_w_excel.html' style=sasweb;

   

ods tagsets.excelxp file='c:\temp\open_xp_w_excel.xml' style=sasweb

    options(doc='Help');

            

proc print data=sashelp.class

     style(header)={background=pink};

run;

  

ods _all_ close;

  Depending on what you want to do, you have to live with the rules of the destination. When you say you want to "center vertically" -- what do you want to center? A single header cell contents or all the data cell contents? Or do you mean that you want to influence the "center vertically" radio button (for the whole sheet) that is under the Page Layout menus inside Excel? In the above code, you also create an XML file, which you can open with Excel. The TAGSETS.EXCELXP file is an XML file that represents Spreadsheet Markup Language 2003 Microsoft XML. There are suboptions that you can specify which will accomplish things such as changing orientation, or altering the "center vertically" radio button, when the XML file is printed from Excel. These suboptions are written to the SAS log when you use the doc='Help' suboption, as shown in the above code.

  With TAGSETS.EXCELXP, my general rule of thumb is that I use STYLE= overrides to impact colors and fonts and possibly formats of the information that is coming from SAS. I use suboptions with TAGSETS.EXCELXP, to impact Excel menu choices or overall treatment of the sheet. The "Under the Hood" presentation and zip file that I posted previously has information and useful links about using suboptions with Excel. For more information about the types of STYLE attributes that you can change, refer to the documentation topic entitled 'Style Attributes and Their Values'.

  So, the answer to your question is "it depends" -- if I needed to make a header cell italic or center text in a header cell either horizontally or vertically, I would use a STYLE= override. If I needed to impact the "center vertically" radio button (which impacts the whole sheet), I would 1) use TAGSETS.EXCELXP as my destination and 2) use the center_vertically='on' suboption for TAGSETS.EXCELXP. If you wanted to know how to get Excel to respect leading zeroes in a variable value, I would use the TAGATTR style attribute, specifically with TAGSETS.EXCELXP. I presented a paper at SAS Global Forum this year (http://support.sas.com/resources/papers/proceedings11/266-2011.pdf) that showed how to use either TAGATTR or HTMLSTYLE style attributes with Excel. Other papers have been written on the same subject.

  My recommendation always is to learn what SAS can do (learn to use STYLE= overrides with PRINT, REPORT, TABULATE) and then, work to understand how your destination of interest deals with the STYLE information that you can send from SAS. In general, for most destinations, SAS does not provide a mechanism for you to interact with pull-down menus or options that are "inside" the application menu. TAGSETS.EXCELXP is unique in this regard -- through the available suboptions that you can specify. (To some extent, PDF is also unique in this regard, since SAS has system options that you can specify to alter the intial view of the PDF results when the ODS PDF file is first opened in Acrobat Reader. ) If I had to worry about delivering printable content, I would use PDF because 1) it is designed to be printed and 2) the contents are generally not editable unless you have a "full" version of Acrobat.

         

cynthia

🔒 This topic is solved and locked.

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

Discussion stats
  • 13 replies
  • 1881 views
  • 1 like
  • 3 in conversation