[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: DDE to excel and Zw. format
From:       Quentin McMullen <QuentinMcMullen () WESTAT ! COM>
Date:       2002-07-31 19:33:02
[Download RAW message or body]

When I suggested he could format a cell in Excel as text, I meant open a
spreadsheet, select a cell, then format menu -->cells--> text.

The default format seems to be numeric.  If I enter 001 into such a cell, I
see 1.  If I format the cell as text and the enter 001, I see the leading
zeros, and it is left-aligned.  I had understood this to mean that by
changing the format of cell, I had in fact changed what SAS would call the
TYPE of the cell.  But I now have doubts...

If I enter 1 in a default cell it returns true for ISNUMBER.  If I then
change the format to text, it aligns it left, but still returns TRUE (doh!).
But if I now enter 1 into the cell again, it returns false.  Maybe the cell
format is something in-between a format to present the data and an input
mask?  I don't use Excel that much...

Kind Regards,
--Quentin


> From: Howard_Schreier@ITA.DOC.GOV [mailto:Howard_Schreier@ITA.DOC.GOV]
>
> I'm still not sure what it means to "format" a cell as text.
>
> One cannot tell from display appearance whether Excel
> considers a value
> consisting of a string of digits to be number or text. It is
> necessary to
> use the ISNUMBER or ISTEXT function.
>
> On Wed, 31 Jul 2002 13:31:37 -0400, Kevin Viel
> <kviel@EMORY.EDU> wrote:
>
> >Quentin McMullen wrote:
> >
> >> Hi Kevin,
> >>
> >> It seems to me that this is an Excel issue, not SAS.
> >>
> >> When I code:
> >>   filename data dde "excel|sheet1!r3c1:r3c1";
> >>   data _null_;
> >>     file data;
> >>     var=123;
> >>     put var z5.;
> >>   run;
> >
> >
> >Quentin,
> >
> >        Thanks.  However, I seem to still have a problem.  I am at a
> different
> >computer which has Excel97.  I ran the following piece of code:
> >
> >data _null_;
> > var1=321;
> > format var z5.;
> > var2="00123";
> > file gait_xl;
> > put var1 "09"x var1 "09"x var2 "09"x var2;
> >run;
> >
> >        I formatted the first and third cells as TEXT.  The results
> >follow:
> >
> >321     321     00123   123
> >                ^^^^^
> >
> >        Of interest, SAS did not write the Z5. formatted
> value of var1
> >to either cell using Excel97.
> >However, the simple manipulation I wrote earler, presumably
> with other
> >posts, will allow me to write it as required.
> >
> >
> >Regards,
> >
> >Kevin

[prev in list] [next in list] [prev in thread] [next in thread]