PROC IMPORT IN SAS - HOW DO I READ A EXCEL FILE IN SAS?

PROC IMPORT make it easy for you to read different type of data files. It can scan your data file and determine the types of variable (character or numeric) automatically, and assign proper lengths to character variables, and it can also recognize some date formats. PROC IMPORT treats two consecutive delimiters in data file as missing value, It read values enclosed by quotation marks, and assign missing values to variables when it runs out of data on a line. Also, if you want, you can use the first line in your data file for the variable names. 

Simplest Syntax of the IMPORT procedure is

PROC IMPORT DATAFILE = ’filename’ OUT = data-set;

DBMS = identifier REPLACE;

SHEET=<NAME OF SHEET>

RUN;

If your file does not have the proper extension, or your file is of type DLM, then you must use the DBMS= option in the PROC IMPORT statement. Use the REPLACE option if you already have a SAS data set with the name you specified in the OUT= option, and you want to overwrite it.

The IMPORT procedure will, by default, get variable names from the first line in your data file. If you do not want this, then add the GETNAMES=NO statement after the PROC IMPORT statement. PROC IMPORT will assign the variables the names VAR1, VAR2, VAR3, and so on. Also if your data file is type DLM, PROC IMPORT assumes that the delimiter is a space. If you have a different delimiter, then specify it in the DELIMITER= statement. The following shows both these statements: 

Example

Here is a Sample Data for a Superstore: 

sas dataset download

Sample Superstore Data

Lets Try to import this in a SUPERSTORE Dataset

PROC IMPORT DATAFILE='/home/../New Folder/Sample - Superstore.xls'

DBMS=XLS

OUT=WORK.SUPERSTORE;

GETNAMES=YES;

RUN;

LOG: 

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

70

71 PROC IMPORT DATAFILE='/home/../New Folder/Sample - Superstore.xls'

72 DBMS=XLS

73 OUT=WORK.SUPERSTORE;

74 GETNAMES=YES;

75 RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with

options MSGLEVEL=I.

NOTE: The import data set has 9994 observations and 21 variables.

NOTE: WORK.SUPERSTORE data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.05 seconds

user cpu time 0.04 seconds

system cpu time 0.01 seconds

memory 7090.71k

OS Memory 35640.00k

Timestamp 12/19/2020 08:52:35 PM

Step Count 24 Switch Count 2

Page Faults 0

Page Reclaims 1831

Page Swaps 0

Voluntary Context Switches 21

Involuntary Context Switches 0

Block Input Operations 6536

Block Output Operations 7432

OUTPUT:

You can Also Specify the Range of your Data you want to Import from Excel,

Example-

PROC IMPORT DATAFILE="filename" | TABLE="tablename" OUT=<libref.>SAS-data-set <(SAS-data-set-options)> <DBMS=identifier><REPLACE> ;

RANGE="sheet1$A1:C20";

sheet="Sheet1"

GETNAMES=yes;

run;

If you Specify Getnames=no; in Proc Import than PROC IMPORT will not consider your First row as Variable Name, It will treat First Row as Actual Data.

PROC IMPORT DATAFILE='/home/../New Folder/Sample - Superstore.xls'

DBMS=XLS

OUT=WORK.SUPER_NOCOL;

GETNAMES=NO;

RANGE="ORDERS$A1:B14";

RUN;

LOG:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

70

71 PROC IMPORT DATAFILE='/home/../New Folder/Sample - Superstore.xls'

72 DBMS=XLS

73 OUT=WORK.SUPER_NOCOL;

74 GETNAMES=NO;

75 RANGE="ORDERS$A1:B14";

76 RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with

options MSGLEVEL=I.

NOTE: The import data set has 14 observations and 2 variables.

NOTE: WORK.SUPER_NOCOL data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.01 seconds

user cpu time 0.00 seconds

system cpu time 0.01 seconds

memory 5722.62k

OS Memory 32824.00k

Timestamp 12/19/2020 09:09:50 PM

Step Count 30 Switch Count 2

Page Faults 0

Page Reclaims 1416

Page Swaps 0

Voluntary Context Switches 14

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 264

OUTPUT: