topic Re: ExcelXP – Need column to all be text. in ODS and Base Reporting

ExcelXP – Need column to all be text.

deleted_user — Thu, 19 Jul 2007 15:11:39 GMT

I have just started to learn about ExcelXP.

The code I’m using.
ods listing close ;
ods path sashelp.tmplmst(read);
ods tagsets.ExcelXP file="tsp_01.xml" style=Statistical ;
proc print data=work.temp01 noobs ;
id var1 ;
by var1 ;
run ;
ods tagsets.ExcelXP close ;
ods listing ;

The problem I haven’t been able to find a fix for is the second variable in my dataset is a mixture of numbers and characters so when I input the file into excel some of the variable’s in the column move to the right and the others move to the left. I would like all the values in that column to be characters. How do you do this?

Thank you.
Timothy

Re: ExcelXP – Need column to all be text.

OS2Rules — Thu, 19 Jul 2007 15:31:02 GMT

Hi:

You need to add an Excel tag using the STYLE options to format the variable on the spreadsheet.

I'm not exactly sure how to do it in PROC PRINT, but here is how I do it with PROC REPORT (where we have VAR2 as both numeric and text):

proc report nowd split='*' box;
column var1
var2
var3;

define var1 / display 'VAR1' ;
define var2 / display 'VAR2' style(column)=[tagattr='format:@'];
define var3 / display 'VAR3' ;

run;

You an find the STYLE options in PROC PRINT chapter of the online DOC.

Re: ExcelXP – Need column to all be text.

deleted_user — Thu, 19 Jul 2007 15:51:06 GMT

Thanks for the help.

I tried using PROC REPORT and something strange happened, all the values turned to 0. The column only has 1 character per cell either alpha or numeric but now it only show 0's.

I also found the same style option in the PROC PRINT document and tried it as well and it had the same problem. I did find the following while looking through the XML file.

N
0.97463

I removed ss:Formula="0" from the XML file and reloaded the file into Excel and it displayed correctly. Does any one know why or how that ss:Formula="0" was added to the XML file, it appears to be what is causing the problem.

Thanks.

Message was edited by: TSP

Re: ExcelXP – Need column to all be text.

deleted_user — Thu, 19 Jul 2007 18:46:01 GMT

The syntax for PROC PRINT is pretty much the same as REPORT.

proc print data=xyz STYLE(column)=[tagattr='format:@'];
or you can put the style after a VAR statement (much like the DEFINE)

VAR x /STYLE(column)=[tagattr='format:@'];

What I'm unsure of is the "format:@" syntax - this might be in the a later edition of the tagset. I used to use (for numbers) "#,###.00"

This you can lookup by submitting ODS TAGSETS.EXCELXP FILE='test.xml' DATA='test.ini' OPTIONS(doc='Help');
using the appropriate file and data values.

The (column) could be other elements as well - HEADER DATA BYLABEL are a couple - these are documented in the online manual (this is a handy bookmark to make) under both PRINT and REPORT.

Just noticed that instead of column in proc print, you would use data. print doesn't have a column location. Message was edited by: rwright

Re: ExcelXP – Need column to all be text.

deleted_user — Wed, 12 Sep 2007 09:02:35 GMT

I use tagattr='format:text' but it is probably the same as the @ option.

One difficulty with text is that the word wrapping does not work on the latest version of the ExcelXP tagset (June 2007). I had to edit it to make it work properly. It's got something to do with the additional __r __c and __l elements not inheriting parent attributes, so I removed those DEFINEs, which are new to this version of the tagset compared to the previous one. Message was edited by: Roddy