DATA Step, Macro, Functions and more

DDE and truncate

Reply
Super Contributor
Posts: 673

DDE and truncate

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: DDE and truncate

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

Scott Barry
SBBWorks, Inc.
Super Contributor
Posts: 673

Re: DDE and truncate

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.
SAS Super FREQ
Posts: 8,868

Re: DDE and truncate

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
Super Contributor
Posts: 673

Re: DDE and truncate

Posted in reply to Cynthia_sas
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;
Regular Contributor
Posts: 171

Re: DDE and truncate

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.
Super Contributor
Posts: 673

Re: DDE and truncate

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;
Ask a Question
Discussion stats
  • 6 replies
  • 217 views
  • 0 likes
  • 4 in conversation