DDE and truncate

[SASPhile]

I'm using DDE to export data to excel.
phynumber has leading zeros and they are getting truncated in excel.how to avoid this?
it is defined as phynumber '09'x in the put statement.

[sbb]

You really need to share your SAS-generated code, preferably pasted into your post/reply from your SAS log output.

Scott Barry
SBBWorks, Inc.

[SASPhile]

filename BodyStf dde "excel|[Pharmacy Template.xls]RX Data!targetdata" notab;
data _null_;
set all;
file BodyStf lrecl=15000;
put

phynumber '09'x
Name '09'x
prsc_addr_1 '09'x
prsc_addr_2 '09'x
run;

the dataset all has phynumber as 00102049 but when exported to excel the it is displayed as 102049.

[Cynthia_sas]

Hi:
The '09'x is the hexadecimal equivalent of a TAB character. It looks like you are writing into an Excel file with TABs as the delimiter for the columns. Can you set a custom format in Excel for the phynumber column to show leading zeroes????

The only other way that I know to have Excel respect leading zeroes is to use the Output Delivery System to send a Microsoft format for Excel to use when it opens the file. The method that you use with ODS depends on whether you are using ODS HTML-based methods or XML-based methods, as described in this post:
http://support.sas.com/forums/thread.jspa?messageID=41019ꀻ

It is possible that you could write a command (via DDE) to format the phynumber column, but I don't do much with DDE, so I don't know what such a command would look like.

cynthia

[SASPhile]

There is a way actually to preformat the cell programatically.
c1 is the column that needs the performating.

data _null_;
file cmds;
put '[select("C1")]';
put '[format.number("@")]';
run;

[polingjw]

One method that I have used to solve this problem is to append an invisible character to the every value of the variable before exporting to Excel. To add an invisible character, hold the Alt key and type the digits 255 on the numeric keypad, with NumLock on. Upon import, Excel will recognize the value as text and keep the leading zeros. The only problem is that you will not be able to perform numeric computations on that column from within Excel.

[SASPhile]

There is a way actually to preformat the cell programatically.
c1 is the column that needs the performating.

data _null_;
file cmds;
put '[select("C1")]';
put '[format.number("@")]';
run;