topic ODS ExcelXP tagset - writing out a text field that is sometimes numeric in ODS and Base Reporting

ODS ExcelXP tagset - writing out a text field that is sometimes numeric

deleted_user — Fri, 16 Jan 2009 22:20:43 GMT

I have a field in my dataset that is 8 characters long. Sometimes, all of the characters are numeric and in fact, the first observation in the dataset is 00000001. I use style={tagattr='type:String'} to force the field to be text in Excel, but it still seems to be getting picked up as numeric.

The worst part is that I try a small test program and it works ok. I do a proc contents on the dataset that doesn't work and on the one that works in my test program and both are type char and len 8. So I don't think there's a difference there.

Does anyone have any thoughts on a workaround or a way for me to diagnose or do it differently? I am an XML novice, no doubt.

Thanks,
--Charlie

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Eric_SAS — Mon, 19 Jan 2009 15:59:31 GMT

Try

style={tagattr='format:text'}

That should do what you want.

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

deleted_user — Wed, 21 Jan 2009 19:39:09 GMT

I was thinking I had tried that, but gave it another shot. It unfortunately does not do the trick. Any other ideas?

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Cynthia_sas — Wed, 21 Jan 2009 20:24:29 GMT

Hi:
When I try this code:
[pre]
data charstr;
infile datalines;
input name $ charstr $ state $;
format name charstr $8. state $2.;
return;
datalines;
Alan 0000001 OH
Bob 0000002 AL
Carl 0000003 NC
;
run;

proc contents data=charstr;
run;

ods tagsets.excelxp file='c:\temp\charstr.xls'
style=sasweb;
proc print data=charstr;
var name;
var charstr /
style(data)={tagattr="Format:text"};
var state;
run;
ods _all_ close;

[/pre]

and open the resulting Spreadsheet Markup Language file with Excel, my number is stored as text. Excel (in Office 2007) shows me that the number is stored as text with a little green triangle in the upper left hand corner of the cell. Excel is not happy about storing a number as text.

If I change the DATALINES to this:
[pre]
datalines;
Alan 0000001 OH
Bob 0000002 AL
Carl 0000003 NC
Dave Nothing GA
;
[/pre]

Then Excel does NOT put the green triangle in the cell for the string "Nothing", but still does put the green triangle (warning of number stored as text) for the first 3 observations.

If by "not work" you mean that you're getting the green triangle, this is the expected behavior. As far as I know, you have to manually "clear" that triangle by selecting a drop down box and choosing "Ignore Error" on the cells with the green triangle.

If by "not work" you mean that you're getting the numbers without leading zeroes, as numbers in the Excel worksheet, then you might consider working with Tech Support on this issue.

cynthia

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

deleted_user — Wed, 21 Jan 2009 22:13:03 GMT

Thanks Cynthia. By "not works", I mean that it trims the leading zeros, not the green triangle thing. I'll talk to Tech Support.

--Charlie

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

deleted_user — Wed, 21 Jan 2009 22:47:49 GMT

OK, I got it. I was also overriding the width of the column with:

style(data)={cellwidth=250}

For some reason, it doesn't like that. When I remove that, it works. I tried out your (Cynthia's) test code and it worked for me, so was able to back into the diagnosis.

Thanks for your help.

--Charlie

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

genkiboy — Thu, 22 Jan 2009 20:07:24 GMT

I'm not going to belabor this because I don't have a business need for it at the moment, but running Cynthia's code as-is I get numbers in my Excel spreadsheet. I'm using Excel 2003 and SAS v 9.1.3 in case that matters.

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

deleted_user — Thu, 22 Jan 2009 20:55:44 GMT

Yes, that was my last post. I get it to work as well with Cynthia's code. That helped me figure out that the problem has something to do with setting the width of the column. When I put that override on, it doesn't work, but I'm ok with taking it off. So thanks.

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Cynthia_sas — Fri, 23 Jan 2009 03:26:47 GMT

Hi:
I no longer have Excel 2003 to test with. All my computers have Excel 2007. It's possible this is an issue with 2003 vs 2007. (Most likely)

If and when you do have a need for this, you should work with Tech Support if my code does not give you the results you expect (number as text). As far as I know, they could test in Excel 2003 for you and help pinpoint the likely cause.

cynthia

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

data_null__ — Fri, 23 Jan 2009 12:48:58 GMT

I have EXECL 2003 and get the proper result with when using the updated TAGSET.

With this really old verion NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.28, 08/29/05). TAGATTR seems to be ignored.

Upgrading to NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08). I get the correct result.

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

genkiboy — Fri, 23 Jan 2009 14:34:08 GMT

Thanks, Cynthia and data _null_. I updated my tagset, and now Cynthia's code gets me the desired results.

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

deleted_user — Thu, 29 Jan 2009 18:17:45 GMT

Charlieh,

I have run into this problem frequently with 10 digit account numbers that often begin with leading zeroes. My solution is by no means elegant, but it works. I simply create a new variable with an apostrophe in the first position followed by the account number. That forces Excel to treat it as the character value it is, and the leading zeroes are retained.

HTH
Paul

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

deleted_user — Thu, 29 Jan 2009 18:58:01 GMT

Thank you. This issue has been resolved. It had to do with my overriding the cellwidth. When I remove that override, Cynthia's sample code works just fine. So does my final code.

As to putting the apostrophe on the end, that's exactly where I first went but didn't like it as a long-term solution.