Date: Thu, 15 Jun 2000 11:54:36 -0400
Reply-To: Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject: Re: Using DDE to read data from Excel
It's important to understand that DDE input uses the "regular" buffer. I've found
that having the INPUT statement process a field as numeric is no more troublesome
with DDE than it is when the INFILE points to a plain text file.
Sometimes it helps to see what is in the buffer, since it's not always what you
think is there. Try inserting
immediately after the INPUT statement.
On Thu, 15 Jun 2000 14:00:22 GMT, Paul Thomas <radium_x@MY-DEJA.COM> wrote:
>I'm not sure why you are getting zeros instead of missing, SAS reads in
>what Excel displays rather than the 'actual' cell value, so this may be
>the cause of the problem. Try adding DSD to your infile line
>which handles double delimiters as a missing column.
>I would also advise that you read in all data as a character, and then
>convert to numerics with input statements as I find this usually leads
>to less problems in the long run.
>My typical excel reading program looks something like this.
>/* Open excel program - using DOS path name is important*/
>options noxwait noxsync;
>/* You may need to wait for Excel to start */
>data _null_; x=sleep(5); run;
>filename exsys dde 'Excel|System';
>/* Open excel sheets */
> file exsys;
> put '[Open("S:\Database\Excel.xls")]';
>filename exlin dde "Excel|S:\Database\[Excel.xls]Sheet1!R2C1:R258C3;
> infile exlin dlm='09'x notab dsd truncover;
> input a$ _b$ _c$;
> /* Convert variables from char to num */
> if not verity(_b,'0123456789. ') then b=input(_b,best.);
> if not verity(_c,'0123456789. ') then c=input(_c,best.);
> drop _:;
>/* Close excel */
> file exsys;
> put '[Quit()]';
>/* Clear filenames */
>filename exlin clear;
>filename exsys clear;
>options xsync xwait;
>In article <firstname.lastname@example.org>,
> Vidya <vidya.chandrasekaranNOviSPAM@wipro.com.invalid> wrote:
>> Hi Richard
>> Thanks for the response.
>> Can u help me regarding handling blanks in excel data?
>> I tried the following statements to preserve blanks.
>> FILENAME xlsdata DDE 'excel|d:\sas\vidya\[delimit.xls]sheet1!
>> R1C1:R100C26' notab;
>> INFILE xlsdata dlm='09'x;
>> This resulted in filling the blanks with 0.00 instead of null in
>> the dataset.
>> Could u tell me what si wrong?
>> * Sent from RemarQ http://www.remarq.com The Internet's Discussion
>> The fastest and easiest way to search and participate in Usenet -
>Sent via Deja.com http://www.deja.com/
>Before you buy.