The SAS Output Delivery System and reporting techniques

ExcelXP with Tabulate

Accepted Solution Solved
Reply
Super Contributor
Posts: 358
Accepted Solution

ExcelXP with Tabulate

Hi All:

I have a couple of little problems with ExcelXP and PROC TABULATE that I can't seem to find the answers for.

Firstly, when I use ExcelXP tagset with the MINIMAL template, my group lables are at the bottom of the groups.  Using the HTML tagset, they are at the top of each group.  Is there a way I can force ExcelXP to put them at the top?

Secondly, when I have the lowest-order variable label printed, the label is printed for each group, not just once at the top of the rows - can this be fixed as well?

I realize that my descriptions are a not exactly clear, so here is my TABULATE....  (sorry for the formatting) -

  proc tabulate data=extract4 missing order=data format=6.;     

  class group userid via_category trans_group Trans_type; 

  table group='Group' * (userid='User' all='Group Total') * (via_category='' all='User Total') ,

        trans_group='' * trans_type='' * n=''          

        all='Total'*n='' / rts=14;       

  title1 j=c "Table";

  run;

The labels "User" and "Group Total" appear at the bottom of the column, not the top.  And if I use "via_category='VIA'  " then "VIA" appearson each USERID group - I would only like to see it one at the top.

Thanks in advance.


Accepted Solutions
Solution
‎11-30-2011 11:07 AM
SAS Super FREQ
Posts: 8,864

ExcelXP with Tabulate

Hi:

  OK, so now, go back and look at my screenshot. Where you have BDTS and ABCD, I have G1 and G2. My screenshot shows 3 outputs -- 2 HTML outputs and 1 XML output -- all opened in Excel 2010.

   In the HTML output (from ODS HTML and ODS MSOFFICE2K), the G1 and G2 are "top" justified vertically. In the TAGSETS.EXCELXP output, Excel ignores the instruction that I gave to set the vertical justification with a style override.

  The G1 and G1 or BDTS and ABCD are "levels" of the CLASS variable. So you send STYLE overrides in the CLASSLEV statement:

classlev userid via_category/ style={vjust=t};

--which is what I showed in my original response. Since the vjust=t doesn't seem to be respected by Excel when it opens the XML file, that's why I recommended that you open a track with Tech Support.

cynthia

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,864

Re: ExcelXP with Tabulate

Hi:

 

  Do you mean that you want the string "Group Total" to appear at the top of the cell??? When I create an HTML file using the style override code below,

classlev userid via_category/ style={vjust=t};

           

and open the HTML file with Excel, the vjust=t is respected. However, when I use TAGSETS.EXCELXP, with the same code, Excel does not appear to use the VJUST=T style attribute override. If you can live with generating an HTML file for this table, that might be the quickest way to get what you want. Otherwise, if you really need to use TAGSETS.EXCELXP, then your best resource will be to open a track with Tech Support on this question to find out whether there is alternative method of specifying the VJUST=T specifically for TAGSETS.EXCELXP.

cynthia

Super Contributor
Posts: 358

Re: ExcelXP with Tabulate

Posted in reply to Cynthia_sas

Cynthia:

Actually, I want the string "Group" (the label for the GROUP variable) to appear at the top of the column - with ExcelXP it seems to be at the bottom, but with ODS HTML it is placed correctly.  When ExcelXP creates the spanned cell (?) for each "group", it puts the label at the bootom rather than at the top - am I explaining this correctly?

I'm thinking that I can just cheat and create the ODS HTML output with a  .xls  extension on the file (rather than a ,html), and fool the user into reading the HTML into Excel when they open the file.  After that, they may have problems with it unless they save it in native Excel format (but I can just balme that on the user, can't I?)

I seem to remember that someone posted a process where an ExcelXP output (which is in xml) is converted automatically into a native Excel file using SAS, but I can't remember where that was..... (anyone?..  Bueller??)

SAS Super FREQ
Posts: 8,864

Re: ExcelXP with Tabulate

Hi:

  Whether you use TAGSETS.EXCELXP or ODS MSOFFICE2K or ODS CSV or ODS HTML -- you are never creating "native" or "true, binary" Excel files. All of the above destinations create ASCII text files in various formats that Excel knows how to open and render.

  TAGSETS.EXCELXP does create Microsoft "flavor" of Spreadsheet Markup Language 2003 XML -- whether you consider this "native" or not is up to you. If you use TAGSETS.EXCELXP to create an XML file and you open the XML file with Excel and then you do a File-->Save AS, Excel will remember that it opened XML and will try to resave the file as an XML 2003 file -- to me that says that you are still saving an ASCII text XML file and not a file in the "true" .XLS format.

  If ODS HTML or ODS MSOFFICE2K give you the type of output you want, and if Excel respects the formatting that you have in the HTML file, then by all means, you can "fool" the Windows registry by naming the file with an .XLS file extension. This does not mean you are creating a binary .XLS file -- it means that you are only causing Windows to launch Excel when the file is clicked on. (Otherwise, with an HTML file, most systems would launch a browser instead of Excel.)

  I'm still having a hard time envisioning what you mean by the label for 'Group'...in this screenshot, I made some fake data and then, used a variation of your code with ODS HTML, ODS MSOFFICE2K and ODS TAGSETS.EXCELXP -- so, in the screenshot, I show the HTML files and the TAGSETS.EXCELXP file opened in Excel....'Group' is in a cell by itself, next to the header for User...I don't get what you want to do unless you want to move the string for 'Group' up into the Box area???

cynthia


Group_label.jpg
Super Contributor
Posts: 358

Re: ExcelXP with Tabulate

Cynthia:

After re-reading my original comments, I realize that I am not describing just what the problem is.

The label for the group variable is printing in the correct position of the spreadsheet - it is the "label value" (?) that is in the wrong place.    Here is a clip from the resulting spreadsheet that illustrates the problem...  The values "BDTS"  and "ABCD" are at the bottom and I am trying to get them to the top of the column (ie: "BDTS" should print in row 6, not row 15).

sample.jpg

Solution
‎11-30-2011 11:07 AM
SAS Super FREQ
Posts: 8,864

ExcelXP with Tabulate

Hi:

  OK, so now, go back and look at my screenshot. Where you have BDTS and ABCD, I have G1 and G2. My screenshot shows 3 outputs -- 2 HTML outputs and 1 XML output -- all opened in Excel 2010.

   In the HTML output (from ODS HTML and ODS MSOFFICE2K), the G1 and G2 are "top" justified vertically. In the TAGSETS.EXCELXP output, Excel ignores the instruction that I gave to set the vertical justification with a style override.

  The G1 and G1 or BDTS and ABCD are "levels" of the CLASS variable. So you send STYLE overrides in the CLASSLEV statement:

classlev userid via_category/ style={vjust=t};

--which is what I showed in my original response. Since the vjust=t doesn't seem to be respected by Excel when it opens the XML file, that's why I recommended that you open a track with Tech Support.

cynthia

Super Contributor
Posts: 358

ExcelXP with Tabulate

Posted in reply to Cynthia_sas

Cynthia:

Thanks of all the help on this (dispite my crude descriptions).

I was thinking of changing my code to a PROC REPORT, but I don't have a fixed number of "across" variable and TABULATE works much better in this case.

I also found the code that converts the ODS output from XML (or HTML) to native Excel  XLS format (see:   http://support.sas.com/kb/43/496.html ).

I think I will use ODS HTM to generate the output then convert it to native XLS format so the client gets foor foormat they are looking for.

SAS Super FREQ
Posts: 8,864

ExcelXP with Tabulate

Hi:

  I suspect that PROC REPORT would have the same issue with not respecting the vjust=t if you used SPANROWS. Although, without SPANROWS, REPORT would normally put the "levels" of the class or group item at the top of the group (not the bottom). But I can understand why you would choose to use ODS HTML. My only recommendation would be to use ODS MSOFFICE2K, which creates Microsoft "flavor" of HTML (which Excel likes) and not W3C "flavor" of HTML4 generated by ODS HTML, which Excel is not happy with. It doesn't make a big difference with STYLE=MINIMAL, but if you wanted some other style you'd get better results with ODS MSOFFICE2K.

  That is a cool program in the SAS note. Thanks for posting the link.

cynthia

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 363 views
  • 0 likes
  • 2 in conversation