I need to export a SAS (9.1) data set to a named range within Excel
(XP) via DDE, but missing numerical values keep getting exported with
non-null values. This causes errors in sorting and lookup functions
within my Excel template. There are about 70,000 of these missing
values, so manual replacement doesn't work and a find-and-replace
macro won't distinguish between blank cells and space-containing
cells. Any suggestions?

OPTIONS NOXWAIT NOXSYNC MISSING='00'x;/*'09'x*/
FILENAME SAS2XL 	DDE 'EXCEL|SYSTEM' NOTAB LRECL=8192;	
DATA  _NULL_;   SET DATA.ORC_CURRENT;

/*format ORC_&Q1_AGO 8.2 ORC_&Q2_AGO  8.2;*/
FILE ORCDATA; 
PUT
ID_RSSD +(-1) '09'x
DATE  +(-1) '09'x
BANK_NAME  +(-1) '09'x
FRB_DISTRICT +(-1)  '09'x
REGULATOR +(-1)  '09'x
PEER_GROUP +(-1)  '09'x;

---

Clarification of Question by redmocha-ga on 18 Jan 2005 13:29 PST

The code above does not produce the desired result, since SAS seems to
flush everything after the first missing cell is encountered (with
'00'x) and SAS fails to export a true NULL when '09'x is exported.

C. Del Anderson

Assuming you have already consulted the SAS documentation and experts
the most viable solution at this point is to add functionality to your
Excel workbook to clean up the exported data so that it can be used
effectively within the Excel environment. Since you have already tried
using the Excel find/replace function you will probably have to resort
to a macro to do the cleanup. Before providing further help with a
macro you will need to post a more detailed description of what the
resulting data looks like after it is placed in Excel by the SAS DDE
operation. Is it possible to see a sample workbook? If so you can post
a sample on any of the free posting sites such as geocities at
http://geocities.yahoo.com/ps/learn2/HowItWorks4_Free.html. Also
describe how you would like the resulting data to be formatted under
ideal conditions.

Kevin

I don't know how this works for payment, but I actually solved the
problem on my own using MISSING='09'x; instead of MISSING='00'x;. The
pointer reversal drops the trailing space and the missing tab
character, but it doesn't affect numeric data. It's a hack, I know,
but it does seem to work.

That looks like less of a hack than using an Excel macro to clean up an SAS mess.

The Google Answers service is still relatively unorganized and
incomplete. As far as I can tell you don't do anything and you will
only be charged a $0.25 listing fee. The only people who actually get
paid are GA authorized experts of which there don't seem to be any in
the Excel and SAS fields. I'm just a member like you trying to help so
I don't get paid. If you want real and fast answers to very specific
questions try www.expertsexchange.com - I'm one of the Excel "experts"
over there. If any GA admins are watching these threads they would be
well advised to look at EE and figure out what they are doing over
there to be so successful.

Kevin

Thanks for the website tip.... except that I initially read the link
as "Expert Sex Change."

C. Del Anderson

I believe others had the same observation and hense their current
address is http://www.experts-exchange.com/. They still own the old
address I gave you above and it redirects correctly to the new.

Kevin