The SAS Output Delivery System and reporting techniques

ExcelXP With Bad Data

Reply
Super Contributor
Posts: 358

ExcelXP With Bad Data

Hi All:

I have some data that I am printing to Excel using the ExcelXP Tagset.

Unfortunately, there is 1 record in the data (a text field) that can have an equal sign ("=") as the first character. This causes an error in the table (I'm using PROC REPORT) because Excel thinks that the field is a formula and not data.

I tried the: tagattr='format:@' to force the field to be text, but Excel still thinks that it is a formula.

Any thoughts on how I can get around this (without translating the input field I mean)?

Thanks in advance.
SAS Super FREQ
Posts: 8,740

Re: ExcelXP With Bad Data

Hi:

My tendency would be to translate the '=' to something like ~ (tilde) or - (dash) or use ods escapechar to insert a unicode symbol into the text string....if the = wasn't required. or, put some text string in front of the whole field like
COMMENT: = or
NOTE: = or
X = or even
| =
I don't know whether inserting an "unprintable" character would work, but that might be something else to try -- on the other hand, Excel might just ignore the unprintable character.


Does the equal sign serve any meaningful purpose??? If forcing the field to be text doesn't work and you really, really need the = to be maintained, then you might really need to work with Tech Support on this question.

Excel has its own way of dealing with cell values and I'm not sure how you get around this one. To send a question to Tech Support, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem". Alternately, you can go directly to the Tech Support Problem Form here:
http://support.sas.com/ctx/supportform/createForm

cynthia
Super Contributor
Super Contributor
Posts: 3,174

Re: ExcelXP With Bad Data

Append a leading single-quote character to your SAS variable (using an assignment statement) to cause Excel to treat the cell as TEXT, regardless of content. You will need to consider the SAS variable length and increase by +1 to accommodate the additional prefix-character.

Scott Barry
SBBWorks, Inc.
Ask a Question
Discussion stats
  • 2 replies
  • 258 views
  • 0 likes
  • 3 in conversation