HOME Module List Site Index About This Site glossary" Resources Innovative Technology Center UT Statistics Courses
Using SAS Choose Design ANOVA Compare Means Regression Examples
Using SAS Using SAS: Table of Contents

Accessing data
PROC Import problems

Most problems with using PROC IMPORT to automatically read an external file into SAS can be avoided by making sure that
      each column has a name in the first row. This name is used by IMPORT to create a variable name. Illegal
        character such as spaces are converted to underscores.

      each column has contents with the same format, eg. all letters or all numbers or all dates.

IMPORT reads the first 10 or so observations to choose a format. A common problem is if those observations are missing. IMPORT then assumes the column is text, and any subsequent numbers in that column are not read as numeric data. If you try running a regression on this variable, an error such as "Data are not valid" will appear in the Log Window. To fix this, sort your data to move non-missing data to the top.

With Excel files, it is possible that IMPORT will decide the file is corrupted. This seems to happen if extensive data manipulation was done in Excel, such as cutting, pasting, hiding columns, using formulas, etc. This problem is most easily noticed by printing the imported data and seeing that all the data were not read in, but strange error messages may also be written in the Log Window. An easy fix if this happens is to copy the spreadsheet, and paste it into a new sheet. If this does not fix the problem, then save the sheet in comma separated format, then open that new file back into Excel and resave as Excel, or let IMPORT read the comma separated file.

IMPORT may have trouble with sheet names. There is a statement SHEET to specify the sheet that you want read (See Accessing Data from Excel), and this may help. Otherwise, rename your sheet to a short name with no spaces. These types of errors usually appear in the Log Window as "Import can not find the sheet named XXXX".

The fundamental error "File not found" means your file name in the DATAFILE option is incorrect. For complex networked drives, the name may be confusing to SAS, and an easy fix is to move the file to a local drive with a short folder hierarchy.

Proc Import also will have problems if a variable (column) contains mostly numbers, but with a few observation having letter values. Import scans the column of data, and will decide the variable should be numeric - as a result the observations with letters are set to missing, and that data is lost. Similarly, if you have a column with lots of missing data (or blanks), SAS interprets that as text data, and then numeric data in those columns is set to missing. The easiest solution to such problems is to save your spreadsheet in comma or tab separated text format (either .csv or .txt). Then use these instructions to read comma or tab text data.

Related Topics:                                                                                                     Using SAS: Table of Contents
Overview
From within a SAS program
From Excel
Accessing data from text files
    tab delimited files
    comma delimited files
PROC Import problems

Home | Contact us | Module list & summary | Glossary/Terms | About this site | Stats courses | Links | Index