Date:         Tue, 18 Jan 2005 17:00:36 -0500
Reply-To:     Nathaniel_Wooding@DOM.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Nat Wooding <Nathaniel_Wooding@DOM.COM>
Subject:      Re: IMPORT NUMERIC EXCEL DATA AS TEXT
Content-type: text/plain; charset=US-ASCII

Another solution which I have not seen would be to use DDE to read the file
while the SAS input code specifies the informat as character.

Here is some sample code:

DM 'OUTPUT;CLEAR;LOG;CLEAR;PGM;';

 OPTIONS NOXWAIT NOXSYNC MPRINT;  * note the noxwait and noxsync!!;



 /* THE DDE LINK IS ESTABLISHED WITH EXCEL               */


DATA _NULL_ ;          * you may have excel stored under a different path;



x " 'C:\Program Files\Microsoft Office\Office\EXCEL.EXE'
   ""c:\park\97jun.XLS"" ";** substitute the name of the workbook that you
want to read;

 *OPEN EXCEL;
RUN;
DATA _NULL_;
 X=SLEEP(5); * PAUSE SAS PROCESSING FOR 5 SECONDS WHILE EXCEL OPENS AND
LOADS THE SHEET;


DATA a;

FILENAME GETDATA DDE "EXCEL|SHEETNAME!R5C2:R32C15"; * note the rows and
columns.  ;
                                               *the portion after the | is
the DDE triplit;
INFILE GETDATA DLM='09'X NOTAB DSD MISSOVER; * note the dlm. this separates
excel fields;

INFORMAT COL1 -COL11 $20.;
INPUT COL1-COL11;

DATA _NULL_;** SHUT DOWN EXCEL;
FILENAME CMDS DDE 'EXCEL|SYSTEM';
FILE CMDS;

PUT '[QUIT()]';
RUN;



In the filename statement, you need to replace SHEETNAME with the name of
the spreadsheet that you wish to read. Also, specify the row and column
where you wish to start reading (my R5C2) and where you wish to end (the
bottom left corner of the sheet).  You will probably want to skip over the
column header lines and start with the data.

Also, modify the informat and input statements.

Nat Wooding