INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...These forums are an excellent source and example of the way people can help each other..."
Geography
Where in the world do Tek-Tips members come from?
|
 SAS report output to Excel.
|
|
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.tplwhich 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:- CODEods 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:- CODEods 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. |
|
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:- CODEputq ' 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 CODE do /if cmp(htmlclass, "Data"); put '<Alignment ss:WrapText="1" ss:Vertical="Top" />' NL ; done; after the line CODEputq '<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:- CODEods 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. |
|
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 |
|
Just a stab in the dark here, would single quotes on the outside solve this problem?
Klaz |
|
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 |
|
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 |
|
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 ". Hence I tried this in the formula:
X = "=IF(R[-2]C=R[-1]C,'"' '"','"'ERROR'"')"
and also,
X = "=IF(R[-2]C=R[-1]C,%nrbquote("); %nrbquote(");,%nrbquote(");ERROR%nrbquote(");)"
But, both failed.
Sarav |
|
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. |
|
OK, this is what the cell looks like in the XML:- CODE<Cell ss:StyleID="data" ss:Formula="=IF(1 > 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:- CODE<Cell ss:StyleID="data" ss:Formula='=IF(1 > 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. |
|
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 CODE 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:- CODE 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:- CODEdata 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. |
|
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 |
|
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:- CODEput '<PaperSizeIndex>0</PaperSizeIndex>' nl; and change it to CODEput '<PaperSizeIndex>9</PaperSizeIndex>' nl; Other valid papersize values and meanings are listed here:- CODE 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=confEnjoy. |
|
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 |
|
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 |
|
Hi Sarav,
Scroll back up to the top, my second post, points 1 and 2 switch on word wrapping and autorowheight. :)
|
|
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 |
|
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). :)
|
|
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 |
|
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 |
|
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. |
|
Found another issue though that I've managed to fix. Well, I think it's an issue, and I'm pretty sure it's fixed. If in a proc report, you do a line statement to put a line out before the table, the tagset tries to calculate how many lines need to be repeated at the beginning (assuming you are using "repeat_rows='header'"). There seems to be a bug in it that causes it to try to start at row -1 instead of row 1. It looks like some brackets are missing from a line of code. Under "event table_body" locate this line:- CODEeval $possible_row_repeat_start $worksheet_row - $row_count - 1; and change it to CODEeval $possible_row_repeat_start $worksheet_row - ($row_count - 1); This seems to fix this problem. |
|
Hi Chris,
I had encountered this problem before and I was wondering what the round about was. Thanks to you, you seem to be going great guns on the EXCEL Tagset. May be this time they wud name the tagset after you :)
Sarav |
|
Hi,
I was wondering if the EXCEL XP Tagset can be used to hide certain columns of a dataset akin to hiding certain columns in EXCEL.
I appreciate any suggestions as to accomplish this.
Thanks,
Sarav |
|
How much digging have you done into the XML that is produced, and also into the tagset itself? It shouldn't be a major piece of work to implement something to do this for you. As an example, a common issue I've had over the years exporting data to Excel is that Excel tends to take account numbers with leading zeros and strip them off, which is often not desirable. This tagset is unable to determine the type of a variable (Proc Report and tabulate does not pass this info to the output) so determines it from the contents of the data. So I wrote in a small piece of code to pick up the "HTMLCLASS" of a column so that I could specify a column as a string. This was added to the end of "define event cell_start" in place of the commented line:- CODE *set $cell_class lowcase(htmlclass); * CHRISW - Cut in favour of the below section *;
* CHRISW - put in check to see if HTMLCLASS has been *;
* set to TEXT. This is an in-house fix. *;
* A flag is set so that I can set the data type to STRING *;
do /if cmp(lowcase(HTMLCLASS), 'text');
set $cell_class 'data';
set $cw_dtype 'Y';
else;
set $cell_class lowcase(HTMLCLASS);
done; and this was added to the end of the "define event value_type;" section :- CODE * CHRISW if my datatype flag is set, set the type to STRING *;
do /if cmp($cw_dtype, 'Y');
set $type "String";
unset $cw_dtype;
done; As a result of this, setting up a report like this:- CODE/* Open up ExcelXP tagset, set options */
ods tagsets.ExcelXP file="ExcelXP_test.xls"
options(default_column_width='8,20,30'
row_repeat='header'
frozen_headers='yes'
debug_level='0')
style=styles.MyExcelStyle;
proc report data=SASHELP.EISMSG(obs=140)
nowd split='*';
columns MSGID
MNEMONIC
TEXT
;
define MSGID /order 'Message*ID' format=z8. style(column)={HTMLCLASS="TEXT"};
define MNEMONIC /display 'Mnemonic' ;
define TEXT /display 'Text Of Message' ;
title 'Test Report';
title2 'Testing new Tagset';
title3 "tagset.EXCELXP";
run;
/* Close */
ods tagsets.ExcelXP close;
ods listing; allows the tagset to change the characteristics of the columns and output the column as a string rather than a number. If you want to hide a column, you could probably do the exact same thing, the difference being you'd need to find the XML code that tells a column that it is hidden (output your file, manually change the column to hidden, save, then view the file in a text editor) and work out what you need to change to get this to work. I'd love to have a go at this, but I'm swamped at the moment, maybe one evening I can have a play at home, but I'm not very motivated to work at home. |
|
Hi Chris,
Thanks for your response and valuable insights. I will certainly try to dig a bit reg. this issue and let you know.
I have in the past seen the issue with EXCEL abt the leading zeros getting truncated in the O/P. Your fix worked great!!!.
Thanks again for your help.
Sarav |
|
No worries. Data vs Excel is always a hassle, it's a problem with alot of PC applications these days - They're just too damned clever. If you don't tell them specifically what to do, they'll make assumptions, and it can be very difficult to tell them not to. As a pointer, here's the bit of the XML you are looking at:- CODE <Worksheet ss:Name="Table 1 - Detailed and or summa"> <Names> <NamedRange ss:Name="Print_Titles" ss:RefersTo="='Table 1 - Detailed and or summa'!R1"/> </Names> <Table ss:ExpandedColumnCount="18" ss:ExpandedRowCount="685" x:FullColumns="1" x:FullRows="1" ss:StyleID="s21"> <Column ss:StyleID="s21" ss:Width="52.5"/> <Column ss:StyleID="s21" ss:Hidden="1" ss:AutoFitWidth="0" ss:Width="60"/> <Column ss:StyleID="s21" ss:Width="37.5" ss:Span="1"/> <Column ss:Index="5" ss:StyleID="s21" ss:Width="60"/> <Column ss:StyleID="s21" ss:Width="112.5"/> <Column ss:StyleID="s21" ss:Width="60" ss:Span="1"/> <Column ss:Index="9" ss:StyleID="s21" ss:Width="150" ss:Span="2"/> <Column ss:Index="12" ss:StyleID="s21" ss:Width="120"/> <Column ss:StyleID="s21" ss:Width="75"/> <Column ss:StyleID="s21" ss:Width="37.5"/> <Column ss:StyleID="s21" ss:Width="120"/> <Column ss:StyleID="s21" ss:Width="52.5"/> <Column ss:StyleID="s21" ss:Width="60" ss:Span="1"/> <Row ss:Height="38.25"> <Cell ss:StyleID="s22"><Data ss:Type="String">...... As you can see, I've hidden the second column. If you do a search in the tagset on the keyword "column" you should be able to start to see where the string is built up. Work out where the Hidden="1" needs to be added, and put in your condition. Good luck. Oh, one thing. Make sure that you don't overwrite your "production" version of the tagset when testing, push it out to the work library. I had one version of this tagset that I completely bollixed, and it would cause my SAS session to crash when I tried to use it. CODEods path work.templat(update) sashelp.tmplmst(read) ; |
|
I noticed that I didn't address one of your earlier questions about creating multisheet workbooks from SAS. You can do this, but not (as far as I'm aware) using ODS. You can actually specify the Excel Workbook as a library, and write to each individual sheets as a member/dataset. To do this you need to have SAS/Access for PC Files.
Enjoy. |
|
Hey Sarav - Did you ever manage to hide an Excel column from ODS? |
|
|
 |
|
Talk To Other Members