Date:         Mon, 26 Sep 2005 17:07:32 -0400
Reply-To:     Wensui Liu <liuwensui@gmail.com>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Wensui Liu <liuwensui@GMAIL.COM>
Subject:      Re: Sas to excel: text variable values beginning with '=' etc
Comments: To: "Wall, Steven" <steve.wall@pioneer.com>
In-Reply-To:  <24FE07B57442DF4DAA5BC3D79DE5402701B7C6FB@jhms10.phibred.com>
Content-Type: text/plain; charset=ISO-8859-1

Steven,

I don't know much about proc print. But if you use proc report, you could
put something like:

proc report
....
define var1 / 'VAR1'
style = {htmlstyle = "vnd.ms-excel.numberformat:@"};
....

Or a simple way is to put a "'" before var1, which should always work.

On 9/26/05, Wall, Steven <steve.wall@pioneer.com> wrote:
>
> SAS-Lers:
>
> I have a dataset that I'm trying to write out to Excel using ODS HTML
> but am having trouble with Excel converting some of my variable values
> during the output operation.
>
> For example, my code looks roughly like this:
>
> ods html style=barrettsblue
> file="&output_folder\&curr_year.POYHU_Final.xls" ;
>
> proc print data=forxls ;
> var Var1 Var2 Var3 ;
> run ;
>
> ods html close ;
>
> And the variable Var1 has some values that Excel thinks are either
> exponential numbers or cell references and when I open the final report
> in Excel things are messed up. In other words:
>
> When Var1 has the text value 8E14, Excel thinks I mean 800000000000000
> and Excel thinks Var1 text values like =NK2555 and =NK8970 are bad cell
> references and converts them to #NAME?.
>
>
> 8.00E+14
> 9N60943-C
> 9YCGRX1522
> 9YCGRX1540
> 9YCGRX1568
> 9YCGX333
> 9YM015W
> 9ZMBS1W/9ZMBS2W
> 0
> 0
> 0
> #NAME?
> #NAME?
> #NAME?
>
>
> Is there a good way around this? I'm tryin to avoid using DDE if at all
> possible and there are some other good reasons I have for not using PROC
> EXPORT. Any ideas would be welcome though.
>
> Since this is for a client without PC-SAS, I'm using Enterprise Guide
> V3.0.
>
> Thanks.
>
> Steve W
>
>
> This communication is for use by the intended recipient and contains
> information that may be privileged, confidential or copyrighted under
> applicable law. If you are not the intended recipient, you are hereby
> formally notified that any use, copying or distribution of this e-mail, in
> whole or in part, is strictly prohibited. Please notify the sender by return
> e-mail and delete this e-mail from your system. Unless explicitly and
> conspicuously designated as "E-Contract Intended", this e-mail does not
> constitute a contract offer, a contract amendment, or an acceptance of a
> contract offer. This e-mail does not constitute a consent to the use of
> sender's contact information for direct marketing purposes or for transfers
> of data to third parties.
>
>
>
> Francais Deutsch Italiano Espanol Portugues Japanese Chinese Korean
>
>
>
> http://www.dupont.com/corp/email_disclaimer.html
>



--
WenSui Liu
(http://statcompute.blogspot.com)
Senior Decision Support Analyst
Cincinnati Children Hospital Medical Center