topic Re: Problem of use libname to associate excel in SAS Procedures

Problem of use libname to associate excel

Mike_Davis — Mon, 13 Aug 2012 19:59:50 GMT

Hello everyone,

I have a Excel file named ALL, and sheet named SHEET1.

Not like using proc import ,,,when I use libname to connect excel file to SAS such as:

libname xl Excel "C:\Temp\ALL.xls"; I can get a SAS dataset xl.SHEET1.

BUT if the first 8 obs of a column is empty, then this column will be set to length $1. and all the numeric value of this column will be set to empty.

So if I still want to use libname I have to set a value in the first 8 obs.

Please give me some suggestion on it!

Thanks!

Mike

Re: Problem of use libname to associate excel

PGStats — Mon, 13 Aug 2012 20:16:28 GMT

This is a bit messy, but fortunately, you will have to do this only once. The problem lies with the number of lines that Excel scans to determine the properties of columns. You must change the TypeGuessRows entry in Windows registry. This is described on page 36 of the document :

http://support.sas.com/documentation/cdl/en/acpcref/63184/PDF/default/acpcref.pdf

Re: Problem of use libname to associate excel

Mike_Davis — Mon, 13 Aug 2012 20:32:40 GMT

Thanks PG,

but I don't have the permit to change the computer's setting,,,,,,so what I can do only is add an extra row on the top to define the columns' properties?

Thanks

Mike

Re: Problem of use libname to associate excel

PGStats — Mon, 13 Aug 2012 22:04:26 GMT

Bt default, Excel scans only the first 8 lines of a table to determine the column properties. So, yes, putting your longest possible string within the first 8 lines will do the trick. - PG

Re: Problem of use libname to associate excel

Ksharp — Tue, 14 Aug 2012 04:11:24 GMT

Did you try some option similar to proc import ?

libname x excel 'c:\xx.xls' scantext=yes mixed=yes;

Re: Problem of use libname to associate excel

Mike_Davis — Tue, 14 Aug 2012 21:27:34 GMT

Thanks Ksharp,

I had tried these options before

I am sure this is not a new problem, if you could find an option like that would be fantastic!

Thanks

Mike