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

   PUT _INFILE_;

immediately after the INPUT statement.

On Thu, 15 Jun 2000 14:00:22 GMT, Paul Thomas <radium_x@MY-DEJA.COM> wrote:

>Vidya,
>
>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;
>x 'd:\progra~1\micros~1\office\excel.exe';
>
>/* You may need to wait for Excel to start */
>data _null_; x=sleep(5); run;
>
>filename exsys dde 'Excel|System';
>
>/* Open excel sheets */
>data _null_;
> file exsys;
> put '[Open("S:\Database\Excel.xls")]';
> run;
>
>filename exlin dde "Excel|S:\Database\[Excel.xls]Sheet1!R2C1:R258C3;
>
>data excel;
> 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 _:;
> run;
>
>
>/* Close excel */
>data _null_;
> file exsys;
> put '[Quit()]';
> run;
>
>/* Clear filenames */
>filename exlin clear;
>filename exsys clear;
>options xsync xwait;
>
>HTH
>
>Paul.
>
>
>In article <0023a388.f83644bc@usw-ex0104-028.remarq.com>,
>  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
>Network *
>> The fastest and easiest way to search and participate in Usenet -
>Free!
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.