topic DDE and truncate in SAS Programming

DDE and truncate

SASPhile — Tue, 17 Aug 2010 20:07:29 GMT

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.

Re: DDE and truncate

sbb — Tue, 17 Aug 2010 20:16:02 GMT

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

Scott Barry
SBBWorks, Inc.

Re: DDE and truncate

SASPhile — Tue, 17 Aug 2010 20:21:05 GMT

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.

Re: DDE and truncate

Cynthia_sas — Wed, 18 Aug 2010 00:36:25 GMT

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

Re: DDE and truncate

polingjw — Thu, 19 Aug 2010 16:10:59 GMT

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.

Re: DDE and truncate

SASPhile — Thu, 19 Aug 2010 16:42:57 GMT

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;

Re: DDE and truncate

SASPhile — Thu, 19 Aug 2010 16:43:14 GMT

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;