Here is an example of how it works with the tagset changes I made this morning. The example sashelp.class report is silly because I didn't order it, but it does show a lot of underlined sumary lines. I don't see a way of attaching anything here. Send me direct email at ODS@sas.com and I'll send you the new tagset that you'll need for this to work. You could avoid the style definition and do all style over-rides but this will create more compact and efficient Excel XML. Eric proc template; define style styles.mystyle; parent = styles.default; style myheader from header / foreground=black font_size=3 just=c ; style whiteheader from header / foreground=black background=white ; style silverheader from whiteheader / background=silver ; style summary / foreground=BLACK text_decoration=underline ; end; run; ODS TAGSETS.EXCELXP style=mystyle FILE="test.xls" OPTIONS(EMBEDDED_TITLES='YES' SHEET_NAME='WEEKLY' FROZEN_HEADERS='3'); RUN; /* PROC REPORT NOWD DATA=W_W STYLE(HEADER)=myheader STYLE(COLUMN)=whiteheader STYLE(SUMMARY)=silverheader; TITLE "NH WEEKLY METRO"; COLUMN MARKET NAME REPORTING COUNTY NITHHWK1-NITHHWK4; DEFINE MARKET / GROUP RIGHT; DEFINE NAME / GROUP; DEFINE REPORTING / GROUP; DEFINE COUNTY / ORDER CENTER; DEFINE NITHHWK1 / SUM '1 NIT'; DEFINE NITHHWK2 / SUM '2 NIT'; DEFINE NITHHWK3 / SUM '3 NIT'; DEFINE NITHHWK4 / SUM '4 NIT'; BREAK AFTER MARKET/ SUMMARIZE SUPPRESS SKIP STYLE(SUMMARY)=summary; RUN; */ PROC REPORT NOWD DATA=sashelp.class STYLE(HEADER)=myheader STYLE(COLUMN)=whiteheader STYLE(SUMMARY)=silverheader; TITLE "NH WEEKLY METRO"; COLUMN NAME sex age weight; DEFINE sex / GROUP RIGHT; DEFINE NAME / GROUP; BREAK AFTER sex/ SUMMARIZE SUPPRESS SKIP STYLE(SUMMARY)=summary; RUN; ods _all_ close; ... View more

There is a style attribute called text_decoration that will do what you want. The value should be set to underline. The only problem is that the excel tagset does not currently support text_decoration. I will look into adding it and post back with what I find. ... View more

hello again, 1. You should get sheet names with by values if you set your sheet_interval="bygroup" If that is still not quite what you want we can make a simple change to the tagset to make it print what you do want. 2. This should work, I don't see anything standing out. It would be better to define a style to use instead of using attribute over rides. The error indicates that a style definition is possibly missing from the definition. That could happen if you referenced a style element that didn't exist. Does the error file say anything more? For your example I would recommend this. It could fix the problem too. proc template; define style styles.test; parent=styles.default; style lowtraffic / foreground=yellow just = center font_weight=bold ; style regtraffic from lowtraff / foreground=white ; end; run; proc format; value lowtraff low-<0.255='lowtraffic' 0.255-1='regtraffic'; run; ... define low25r / style(column)=lowtraff; ... 3. Percent formats always use a percentage format. I believe that 2 decimals is the default. To change it you could do this. define low25r / style(column)=lowtraff[tagattr='type:Number Format:0.0%']; 4. justification as an over-ride does not work. You can do it through the style definition the way I did for question 2. ... View more

One choice is to use the excelxp tagset. It cannot write to existing workbooks or worksheets. But it can create them with formatting and style so it should be possible to get your Excel files to be the way you want straight from SAS. It does not currently handle superscripts. But if we know what the xml should look like we can make it do superscripts. Providing that Microsofts XML definition supports them. The Excel tagset does support links so it is possible create drill down reports like what you want. By default it creates multiple worksheets, one per table. But that can also be controlled. Take a look at the various resources on the odsmarkup page. http://support.sas.com/rnd/base/topics/odsmarkup/ ... View more

Instead of greplay, you may want to take a look at the html panel tagset. http://support.sas.com/rnd/base/topics/odsmarkup ... View more

This behavior is unavoidable. The tagset uses a complex regular expression to determine if numbers are numbers. The formatting must be stripped because Excel will turn it into a string if there are any currency symbols, comma's, percent signs etc. The appropriate excel format must then be used in order to get the format you want. Percentages are done for you. The tagset finds the % so it knows to change the format to percentage. In the other cases you will need to apply a format if the default format is not what you want. ... View more

The new tagset is now on the web... http://support.sas.com/rnd/base/topics/odsmarkup/excltags.tpl ... View more

you can use tagattr="format: @" When you do that, the type will also be changed from number, to string. This only works in the latest versions of the tagset. The newest version will be available later today. it's worth waiting for because it fixes a couple of bugs reported by Jack earlier this week. ... View more

I'm no expert but you should take a look at the XML mapper and the XML libname engine. - they work together. Mapper tells the engine how to map the xml into the dataset. You will probably have to create your own map. ... View more

If you use a percent format the tagset will figure that out and do the right thing. That has been in place for over a year. I don't know of any problems with getting it to set the type correctly when using percent formats inside SAS. The tagset will set the format to percent and divide the number by 100 automatically. This bug may affect it. The most current tagset will be available sometime tomorrow. ... View more

Well, I documented it incorrectly. It should be Number, not numeric. I changed the documentation and I'll add some error checking so that you'll get a message if an invalid type is used. Number worked for me. number did not. ... View more

Hi jack, There is a bug there. Part of it is that proc report says all those numbers are strings. There is a workaround. tagattr now takes type: You can set type to Number. I do have a tagset fix in hand as well. Here's an excerpt from the help for tagattr. TagAttr Style Element: Default Value '' Values: or This is not a tagset option but a style attribute that the tagset will use to get formula's and column formats. The format and formula's given must be a valid to excel. The rotation must be a valid angle for text. 90 through -90. The Type should be General, String, Numeric, or DateTime It should be unecessary to specify type except when DateTime is being used. A single value without a keyword is interpreted as a format. A formula, format and rotation can be specified together with keywords. There should be no spaces except for those between the two values The keyword and value must be separated by a ':' tagattr='format:###.## formula:SUM(R[-4]C:R[-1]C rotate:90'). ... View more

That's a better response than I would have written... 🙂 ... View more

Hi Jack, I have a fix for you. There were various problems. I'm not sure if it was a compatibility issue with report or a combination of that with a few other strange behaviors. We are currently testing it to make sure it's good to go. The individual table entries are still missing from the contents worksheet. That could be a proc report compatibility issue. I haven't tracked that down yet. It does successfully create both a contents and an index and everything links the way it should. ... View more

I should mention that control templates allow for this sort of thing. The set statement in control templates knows how to handle #byval and #byvar. I don't remember if I allow triggering tagset events from there... I probably should. Connecting all the dots is probably the hard part. It was so easy to do I added it to the set statement in tagsets as well. But not until 9.2. ... View more