SAS report output to Excel. 1

[ChrisW75]

OK, I've been playing around with trying to output a report to Excel using ODS Tagsets. I found this Tagset on the SAS Support site:-

http://support.sas.com/rnd/base/topics/odsmarkup/excltags.tpl

which didn't work right off, I had to change references to "colwidth" (but not "$colwidth") to "width", and it's producing Excel output quite nicely. I've added in a style sheet to set up colours and stuff, and used options to set up various items (column width is a little problematic, I think the amendment I made wasn't correct, though it did at least allow me to run the template).
All good so far. The only problem I'm still having is setting up the wrapping on the cells.
Anyone got any ideas?

Here's the style template:-

ods path work.templat(update) sashelp.tmplmst(read) ;

proc template;
define style Styles.VwaExcel;
   parent = styles.default;
   style SystemTitle from SystemTitle /
      background = #FFFFFF
      foreground = #000000
      font_style = Roman
      font_weight = Bold
      font_size = 4
      font_face = "Arial, Helvetica, Sans Serif";
   style SystemFooter from SystemFooter /
      background = #FFFFFF
      foreground = #000000
      font_style = Italic
      font_weight = Bold
      font_size = 3
      font_face = "Arial, Helvetica, Sans Serif";
   style SysTitleAndFooterContainer from SysTitleAndFooterContainer /
      borderwidth = 0
      background = #FFFFFF
      foreground = #FFFFFF
      font_style = Italic
      font_weight = Bold
      font_size = 3
      font_face = "Arial, Helvetica, Sans Serif";
   style Body from Body /
      rightmargin = 8px
      leftmargin = 8px
      background = #FFFFFF
      foreground = #000000
      font_style = Roman
      font_weight = Medium
      font_size = 2
      font = ("Arial, Helvetica, Sans Serif");
   style RowHeader from RowHeader /
      background = #555555
      foreground = #FFFFFF
      font_style = Roman
      font_weight = Bold
      font_size = 2
      font = ("Arial, Helvetica, Sans Serif");
   style Header from Header /
      background = #555555
      bordercolor = #000000
      borderwidth = 1px
      foreground = #FFFFFF
      font_style = Roman
      font_weight = Bold
      font_size = 2
      font = ("Arial, Helvetica, Sans Serif");
   style Data from Data /
      borderwidth = 1px
      bordercolor = #000000
      background = #FFFFFF
      foreground = #000000
      font_style = Roman
      font_weight = Medium
      font_size = 2
      font = ("Arial, Helvetica, Sans Serif");
   style Table from Table /
      cellspacing = 1px
      bordercolor = #000000
      borderwidth = 2px
      background = #FFFFFF
      foreground = #000000
      font_style = Roman
      font_weight = Medium
      font_size = 2
      font = ("Arial, Helvetica, Sans Serif");
end;
run;

And here's the test code I'm using to view the results:-

ods path work.templat(update) sashelp.tmplmst(read) ;

ods listing close;
*ods tagsets.ExcelXP file="C:\test\daily_rep.xls";
ods tagsets.ExcelXP file="/apps/dw_house/reports/out/srm/chris/daily_rep2.xls"
    options(default_column_width='5,20,30'
            row_repeat='header'
            frozen_headers='yes')
    style=styles.vwaExcel;
*ods html file="C:\test\daily_rep.htm";

title 'Test Report';
title2 'Testing new Tagset';
title3 "EXCELXP";

proc report data=SASHELP.EISMSG
               nowd split='*';
  columns MSGID
          MNEMONIC
          TEXT
          ;
  define MSGID         /order 'Message*ID';
  define MNEMONIC      /display 'Mnemonic'  ;
  define TEXT         /display 'Text*Of*Message' ;
run;

*ods html close;
ods tagsets.ExcelXP close;
ods listing;

The ODS PATH statement is so that SAS writes the templates to my work library rather than my user library, it means that changes made aren't persistent betwen sessions, which means I don't have to worry about screwing up my user settings.

If anyone has played with this stuff and worked it out, or has time to play with it and get some results, please let me know.

If not, at lteast it's a jumping point for you guys to have some fun with ODS.

 

[ChrisW75]

Well, I got it working late yesterday, it only took my 1.5 days and a support call to SAS

The issue I had with the ExcelXP tagset not working straight off the bat was because it is for SAS 9.1.3 and I was using 9.1.2, so that bit was resolved.

I managed to get word wrapping and auto-row-height adjustment by changing some code in the Tagset as follows...

1 - Cut out this line:-

putq ' ss:Height=' $this_row_height;

from the "define event row_start" section. I just added a * at the start of the line to comment it out, and added in a comment explaining why.

2 - In the "define event style_class" section add

        do /if cmp(htmlclass, "Data");
            put '<Alignment ss:WrapText="1" ss:Vertical="Top" />' NL ;
        done;

after the line

putq '<Style ss:ID=' lowcase(HTMLCLASS) '>' NL;

To use the tagset you need to set up options in the ODS statement, putting options(doc=help) to get a full list of these. The test code I used was as before, but with the ODS statement changed to this:-

ods tagsets.ExcelXP file="/nwunix/daily_rep2.xls"
    options(default_column_width='8,20,30'
            row_repeat='header'
            frozen_headers='yes')
    style=styles.vwaExcel;

Titles and footnotes don't appear on the page, but if you look at the Print Preview they are there on the printer page. Column widths need to be set unfortunately, so you may need to do some playing around there. Row_Repeat tells Excel to repeat the header row on the top of each printed page, and frozen_headers holds th header row at the top of the screen as you scroll through the data.

 

[sarav1981]

Hi Chris,

We have been using EXCEL XP Tagset as a means to create Excel O/Ps in our firm. We are using SAS 9.1.3.

Recently, I was working on a code that involves creating formulas in the EXCEL O/P sheet using the EXCEL XP Tagset. Basically, this can be done by setting the option, Formulas="Yes" on the EXCEL XP Tagset.

The Tagset writes the formulas to EXCEL as XML i.e. it transfers the formulas as references. For example, to define X = SUM(A1:A3), I would specify X = "=sum(R[-3]C:R[-1]C)". I am able to do almost all the formulas to EXCEL this way, but for EXCEL Formulas that contain quotes in them, the tagset is unable to transfer it to EXCEL. For example if the formula is X = +IF(A1=A2," ","ERROR") and when I give it as X = "=IF(R[-2]C=R[-1]C," ","ERROR")", the tagset is unable to recognize the inner " " and "ERROR" due to the quote I give outside.

Can u guide me as to how to accomplish this? I wud owe you big if you can help me on this.

Thanks,
Sarav

 

[klaz2002]

Just a stab in the dark here, would single quotes on the outside solve this problem?
Klaz

 

[sarav1981]

Hi Klaz,

I tried using single quotes on the outside. I get an XML Parse error when I try to open the O/P Excel File.

This is the error message:

XML PARSE ERROR: Missing whitespace between attributes
Error occurs at or below this element stack:
<ss:Workbook>
<ss:Worksheet>
<ss:Table>
<ss:Row>

Alternating, if I try using single quotes on the inside, the formula is transferred to EXCEL but, EXCEL does not recognize the blank and ERROR. So I belive this is not an option for me

Thanks,
Sarav

 

[klaz2002]

perhaps a this may work for you.

x="=IF(R[-2]C=R[-1]C,"||'" ",'||'"ERROR"'||")";

this way your string has the quotes inside it.
Klaz

 

[sarav1981]

Hi Klaz,

I am still getting the same XML Parse error when applying u r format for the formulae. Is there any other way to get the double quotes in there?

Normally, in XML double quotes rea represented by &quot;. Hence I tried this in the formula:

X = "=IF(R[-2]C=R[-1]C,'&quot;' '&quot;','&quot;'ERROR'&quot;')"

and also,

X = "=IF(R[-2]C=R[-1]C,%nrbquote(&quot); %nrbquote(&quot);,%nrbquote(&quot);ERROR%nrbquote(&quot)"

But, both failed.

Sarav

 

[ChrisW75]

sarav - I'll see what I can do, however, in case I get bogged down here, one thing I used to debug when I was working on this tagset was to generate the XML and work out what I had to generate by tweaking the XML code itself. Once I got that part working, it wasn't too complicated to get the tagset to generate it (when I say it wasn't too complicated, it DID take me a day and a half to accomplish the changes, so it wasn't that simple...

I'll have a play and see what I can do.

 

[ChrisW75]

OK, this is what the cell looks like in the XML:-

<Cell ss:StyleID="data" ss:Formula="=IF(1 &gt; 2," ","ERROR")" ss:Index="1"><Data ss:Type="Number"></Data></Cell>

So, as you stated, that doesn't work, nor will it work. I edited this though in notepad to give:-

<Cell ss:StyleID="data" ss:Formula='=IF(1 &gt; 2," ","ERROR")' ss:Index="1"><Data ss:Type="Number"></Data></Cell>

This loads into Excel correctly. What this means is that the tagset needs to be changed to incorporate a single quote rather than a double quote around the formula.
I'll post that in a bit once I've put it in and tested it.

 

[ChrisW75]

OK, I've found it. It took me a while to find where it was putting quotes around the string, then I noticed it was using the putq statement which is what adds the quotes. What you need to do is edit the tagset, and re-load it.

Search for the line

   putq ' ss:Formula=' $formula;

This writes out the string I showed you before with the formula in it. The putq statement adds quotes around the $formula variable, which contains your formula string.
If you change this to:-

   put ' ss:Formula=' "'" $formula "'";

This will put single quotes around the formula. Now, this should work with your formula, however, I've not tested it beyond this, and I don't know if there is ever a time when you'll need to put a single quote in a formula string, if there is, then this will fail, and something more clever will need to be done.
Apparently the \ character is considered an escape character, but when I put this before the double quotes in the formula, it carried these through to Excel and so wasn't really any use. I may be missing something there.

The code I used to test and prove this is as follows:-

data test;

  length X $30;
  X = '=IF(1 > 2," ","ERROR")   ';
run;

ods listing close;
* Open up ExcelXP tagset, set options *;
ods tagsets.ExcelXP2 file="/nwunix/ExcelXP_test.xls"
    options(default_column_width='8,20,30'
            row_repeat='header'
            frozen_headers='yes'
            formulas='yes')
    style=styles.vwaExcel;

proc print data=test noobs;
run;

ods tagsets.excelxp2 close;

As you can see, I actually created a new tagset called ExcelXP2, rather than ExcelXP, just to keep them separate while testing. Also, for some reason it was picking up the old copy of the tagset rather than the new one.

I think I may need to forward these improvements on to SAS.

Let me know how it goes.

 

[sarav1981]

Hi Chris,

You know what, your fix worked like a dream. Thanks a lot for this fix.

I contacted Vince Delgobbo at SAS institute. Vince was the one who developed this tagset in the first place. He was not sure as to what needs to be done. I think you have solved it. You need to report this to SAS and Vince if u get a chance so that they can fix this thing up.

Thanks to Klaz too for his help on this matter. You guys made my visit to this forum an happy one.

Sarav

 

[ChrisW75]

Glad I could help. I forwarded my amendments to the ODS people and asked that they pass them on to Vince as well.
Perhaps they'll be so impressed they'll send me on a Proc Templates course free of charge.... I'd love to work out how to add extra options to the template (Specifically for the paper size), my attempts messily crashed my SAS session.
If you aren't located in the US, you may be interested in another adjustment I've made to the tagset, that of the paper size. By default it is set to "0" which is interpreted as Undefined. For us, that results in it trying to print to "letter" sized paper which is only (I believe) used in the states. To set it to A4, you need to find the line:-

put '<PaperSizeIndex>0</PaperSizeIndex>' nl;

and change it to

put '<PaperSizeIndex>9</PaperSizeIndex>' nl;

Other valid papersize values and meanings are listed here:-

 Paper Size Table                                    
 Index    Paper type               Paper size        
 ----------------------------------------------------
 0        Undefined                                  
 1        Letter                   8 1/2" x 11"      
 2        Letter small             8 1/2" x 11"      
 3        Tabloid                     11" x 17"      
 4        Ledger                      17" x 11"      
 5        Legal                    8 1/2" x 14"      
 6        Statement                5 1/2" x 8 1/2"   
 7        Executive                7 1/4" x 10 1/2"  
 8        A3                        297mm x 420mm    
 9        A4                        210mm x 297mm    
 10       A4 small                  210mm x 297mm    
 11       A5                        148mm x 210mm    
 12       B4                        250mm x 354mm    
 13       B5                        182mm x 257mm    
 14       Folio                    8 1/2" x 13"      
 15       Quarto                    215mm x 275mm    
 16                                   10" x 14"      
 17                                   11" x 17"      
 18       Note                     8 1/2" x 11"      
 19       #9 Envelope              3 7/8" x 8 7/8"   
 20       #10 Envelope             4 1/8" x 9 1/2"   
 21       #11 Envelope             4 1/2" x 10 3/8"  
 22       #12 Envelope             4 3/4" x 11"      
 23       #14 Envelope                 5" x 11 1/2"  
 24       C Sheet                     17" x 22"      
 25       D Sheet                     22" x 34"      
 26       E Sheet                     34" x 44"      
 27       DL Envelope               110mm x 220mm    
 28       C5 Envelope               162mm x 229mm    
 29       C3 Envelope               324mm x 458mm    
 30       C4 Envelope               229mm x 324mm    
 31       C6 Envelope               114mm x 162mm    
 32       C65 Envelope              114mm x 229mm    
 33       B4 Envelope               250mm x 353mm    
 34       B5 Envelope               176mm x 250mm    
 35       B6 Envelope               125mm x 176mm    
 36       Italy Envelope            110mm x 230mm    
 37       Monarch Envelope         3 7/8" x 7 1/2"   
 38       6 3/4 Envelope           3 5/8" x 6 1/2"   
 39       US Standard Fanfold     14 7/8" x 11"      
 40       German Std. Fanfold      8 1/2" x 12"      
 41       German Legal Fanfold     8 1/2" x 13"

I found these listed here:-

http://paggard.com/projects/xls.generator/demo.php?sh=conf

Enjoy.

 

[sarav1981]

Hi Chris,

I am very much in US. To be more precise in NYC. Thanks a lot for your insights into the tagset problem and u r helpful suggestions.

We have been using the EXCEL Tagset for abt 8 months now and we have seen some very interesting changes in them. I am glad to see that you are adding more changes to the tagsets at a rapid pace compared to the SAS ODS guys.

We were using MS Office2k Tagset to get data into EXCEL before that and since we moved to an SAS AIX box, we have been using EXCEL XP Tagset as MS Office2k Tagset uses DDE to transfer data to EXCEL and on UNIX DDE won't work.

Sarav

 

[sarav1981]

Hi Chris,

I need your suggestion regarding EXCEL XP Tagsets again.

I wud like to word wrap all rows in the EXCEL O/P, what changes if any shud I make to the Tagset definition to obtain that?

Thanks in advance for your help.

Sarav

 

[ChrisW75]

Hi Sarav,
Scroll back up to the top, my second post, points 1 and 2 switch on word wrapping and autorowheight.

 

[sarav1981]

Hi Chris,

Thanks a lot. The problem was I was using version 28 of the EXCEL XP Tagset and I was unable to find the syntax you were mentioning in your second post.

I find that the Jan 2006 version 31 of the EXCEL XP Tagset bears this syntax that you are pointing to.

Sarav

 

[ChrisW75]

Yup. Oh, and I got an email back from SAS about the changes I made, and apparently some of them are being integrated into the next version.
I'll be famous! People will bow before me at SMUG (SAS Melbourne User Group), people will point at me in the streets (but this time for good reasons).

 

[sarav1981]

Hi Chris,

Yes...you will be famous and deserve to be

I also forwarded your Formula suggestion to Chevell Parker at SAS Institute. I gave you credit (but only as Chris from Tek-Tips Forum )

Looking forward to your experiments with the tagset and even more opportunities for you to be famous.

Sarav

 

[sarav1981]

Hi Chris,

I have run into another problem in my EXCEL report generation. I am using EXCEL XP Tagset to generate multi-tabbed EXCEL workbooks.

I was requested by my manager to explore the ability of SAS producing protection enabled EXCEL workbooks. Say, there are 10 tabs in the EXCEL workbook, she needs to write protect certain columns (make them as read-only) in a particular sheet.

If you can guide me as to any avenues of protect-enabling in EXCEL O/P s using SAS or for that matter any functions available in SAS, that wud be great.

Thanks,
Sarav

 

[ChrisW75]

Not sure to be honest. My suggestion to you would be this.
1 - Produce a spreadsheet using the tagset.
2 - Open the spreadsheet, protect a column or whatever and save this alongside the oroginal.
3 - Compare the two spreadsheets and find the difference.

This is the starting point. Then, you find a bit of common ground between the two and find the piece of tagset that creates that and see what you can do there. I've not had any training on Tagsets yet, so I've really no idea how to do that as yet.