Date:         Wed, 31 Jul 2002 13:13:14 -0400
Reply-To:     Howard_Schreier@ITA.DOC.GOV
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard_Schreier@ITA.DOC.GOV
Subject:      Re: DDE to excel and Zw. format

1. The problem is on the Excel side. It does not matter how the buffer
which SAS passes to Excel was built. So creating character variables on
the SAS side is pointless.

2. As far as I know, there is no way in Excel to make a data type
declaration, or to "format" a cell as either number or text.

3. The solutions which pass strings like

   ="0123"

to Excel may have unintended consequences. The cells populated this way
will be formulas rather than constants. In other words, they will be
selected if you do

   Edit -> Go To -> Special -> Formulas

This can be remedied if necessary by selecting all of the cells
populated this way, copying them to the clipboard, then doing

   Edit -> Paste Special -> Values

This can be automated via DDE of course.

4. The Excel data-entry convention for forcing a constant to be text rather
than number is to type a leading apostrophe. For example, if you enter

   '123

the result is a text value. The apostrophe does not display, and the LEN
function referencing that cell returns a value of 3.

Unfortunately, when cells are populated in bulk (via DDE, or pasted from
the clipboard), the convention does not work. The apostrophes do
display, and the LEN function counts the apostrophes (though, curiously,
sorts do seem to treat them consistently).

There is a DDE technique which can utilize the leading-apostrophe
convention to force constants to be text. Don't use a DDE triplet.
Instead use the "system" topic and use commands to select and populate
cells one at a time. For example:

   filename commands dde 'excel|system';

   data _null_;
   file commands;
   do val = 1,22,333;
      row ++ 1;
      put '[SELECT("R' row +(-1) 'C1")]';
      put '[FORMULA("' "'" val +(-1) '")]';
      end;
   run;

This places the three values as text constants in the first three cells
of column A in the active worksheet.

This method works, but it is very slow and thus suitable only for small
ranges.

On Wed, 31 Jul 2002 10:06:53 -0400, Quentin McMullen
<QuentinMcMullen@WESTAT.COM> wrote:

>Kevin Viel wrote:
>> The following put the value ="00123" which appeared as 00123 in the
>> cell:
>>
>> filename data dde "excel|sheet2!r5c3:r5c3";
>>
>>  data _null_;
>>  var='="00123"';
>>  file data;
>>  put var;
>> run;
>>
>>         This will provide me a solution, but shouldn't SAS be
>> able write a
>> formatted value, such as z5. to Excel using DDE?  It seems
>> perhaps not,
>> at least using v8.2 and Excel 95.
>>
>
>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;
>
>SAS does send the string 00123 to Excel.  But if you just type 00123 into
an
>Excel cell, it will leave the cell formatted as "general numeric", or
>whatever, and drop the leading zeros.
>
>So your options seem to be:
>1. Send the string ="00123", which will have Excel define it as a text
cell.
>2. Define cell as a text cell (either by hand or via DDE commands) before
>sending the data , then put var z5. (sending the string 00123).
>3. Define cell as a numeric with special format (e.g. 00000), again
>preferably via DDE, then put var (sending the string 123).
>
>I'm still getting my feet wet with DDE, so I'll look forward to
corrections.
>
>Kind Regards,
>--Quentin