/*import and export excel files*/
/*I use this A LOT*/

PROC EXPORT DATA= sashelp.Verbmgr  OUTFILE= "C:\temp\example.xls" 
DBMS=EXCEL2000 REPLACE;
RUN;

PROC IMPORT OUT= exceldata
            DATAFILE= "C:\temp\example.xls" 
            DBMS=EXCEL2000 REPLACE;
     GETNAMES=YES;
RUN;

proc print data=exceldata;
run;


<=== Page: 1 === SAS Consultant === emailed w/answer === 21Oct2005 10:33:44 ===>

Hi Kaz,

For the two xls files that you provided, I included the optional statement MIXED=YES; in my Proc Import code and the data read into SAS.

Behind the scenes, SAS is using the Microsoft Jet Database Engine - an ole db provider - to read and write to Excel.  There is a setting in your pc's Registry for how many rows of an xls column are evaluated by the Jet to determine the prevalent data type - the default setting for this Key is 8 (eight).

If the majority of the values in the first 8 rows of a column are missing, a character data type is assigned and any numeric data will be read in as missing character.

There are several things you can do:

1 - Modify your Proc Import code to include MIXED=YES; so that numeric values will be automatically converted to character if the character data type is assigned

2 - Modify your Proc Import code to specify the actual range of cells to be read, for example,to me it looks like the data you would want from the worksheet 'Local Education' is in cells
B29 - D43, so the code would specify:

  RANGE='Local Education$B29:D43';


like this:

PROC IMPORT OUT= WORK.test 
            DATAFILE= "C:\Documents and Settings\sasszo\Desktop\NLECSR R egional Indicator - Newark, NJ2.xls" 
            DBMS=EXCEL REPLACE;
     SHEET='Local Education$B29:D43'; 
     GETNAMES=YES;
     MIXED=YES;
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;


3 - edit the PC Registry Key TypeGuessRows setting to 0 (zero), so that all rows of the xls column get evaluated by the Jet for Proc Import.
To do this, go to your Windows desktop Start - Run, type in REGEDIT and press OK.

Open (expand) the following Keys:
HKEY_LOCAL_MACHINE / Software / Microsoft / Jet / 4.0 / Engines/ Excel/ 

Now double-click on TypeGuessRows and a popup dialog will appear in which you can edit the (decimal) setting to 0.




I hope this information helps - please let me know if you have other questions about this.

Best Regards,