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.