How to Use PROC IMPORT to Import Data into SAS

Deepanshu Bhalla Add Comment

PROC IMPORT is a powerful SAS procedure that allows you to import data from various external file formats into SAS datasets. It simplifies the process of importing data in SAS. Since PROC IMPORT is commonly used in real-world work scenarios, it is important for every SAS programmer to be familiar with this SAS procedure.

PROC IMPORT

Syntax : PROC IMPORT

Syntax of PROC IMPORT is defined below -

PROC IMPORT DATAFILE=FileName OUT=SASDatasetName
DBMS=identifier REPLACE;
GETNAMES=Yes;
RUN;

Arguments of PROC IMPORT : Explanation

  1. DATAFILE: Specify the location of the file to be imported.
  2. OUT: Specify the name to assign to the dataset after it is imported into SAS
  3. DBMS: Define the format of the file being imported. Some of the common values are CSV, EXCEL, TAB, DLM, ACCESS.
  4. REPLACE: Determine whether to replace the existing SAS Dataset. Yes/No.
  5. GETNAMES: Specify whether to use the first row as variable names. By default it it YES. If you set the option as NO, it will tell SAS not to use the first row of data as variable names. In this case SAS assigns variable names as VAR1, VAR2, VAR3 if there are 3 variables.
File Formats supported in PROC IMPORT

Following is a list of file extensions supported in PROC IMPORT. Specify the value in DBMS=identifieroption

Identifier Output Data Source Extension
CSV Comma separated values .csv
DLM Delimited file .dat or .txt
TAB Tab delimited values .txt
XLS Excel 97-2003 workbooks .xls
XLSX Microsoft Excel 2007 and later .xlsx
EXCEL Supports all versions of MS Excel .xls, .xlsx, xlsb, .xlsm
ACCESS Microsoft Access 2000 and later .mdb
SAV SPSS file .sav
DTA Stata File .dta
JMP JMP files .jmp
Important Note

If you are using SAS OnDemand for Academics, make sure that your file is uploaded to your account before using the PROC IMPORT procedure. Follow the steps shown on this link - Upload Data to SAS OnDemand for Academics

Use PROC IMPORT to Import CSV File

Let's take a simple example to import CSV (comma-separated values) file into SAS. Here we have data named data.csv which has 3 variables and 5 observations. Column names are ID, Name, Score. Data looks like the image below. You can open CSV file in Notepad.

PROC IMPORT - SAS

DBMS=CSV tells SAS that the file being imported is a CSV file.

PROC IMPORT DATAFILE='/home/deepanshu88us0/data.csv'
	DBMS=CSV
	OUT=WORK.READIN;
	GETNAMES=YES;
RUN;

The above SAS Program creates a SAS dataset named READIN in the temporary library (WORK).

NOTE: WORK.READIN data set was successfully created.
NOTE: The data set WORK.READIN has 5 observations and 3 variables.
Output

To see the imported file in RESULTS window, you can use PROC PRINT procedure. See the SAS program below.

PROC PRINT DATA=WORK.READIN;
RUN;
CSV Output

Use PROC IMPORT to Import Delimited File

Suppose you have a delimeted file and you want to read it in SAS. Delimeter is | symbol. Delimited file looks like the image below. Datafile name is data.txt.

PROC IMPORT : Delimited File

DBMS=DLM tells SAS that the file being imported is a delimited file. To specify delimiter, use the DELIMITER='|' option.

PROC IMPORT DATAFILE='/home/deepanshu88us0/data.txt'
	DBMS=DLM
	OUT=WORK.READIN REPLACE;
	DELIMITER='|';
	GETNAMES=YES;
RUN;

We have used REPLACE option to inform SAS to replace the existing SAS dataset READIN.

OUT=WORK.READIN means READIN dataset to be created in the temporary SAS library (WORK).

If you have blank as delimiter in the file, you don't need to use DELIMITER option as default delimeter is blank when you use DBMS=DLM.

Use PROC IMPORT to Import TAB Delimited File

Suppose you have a TAB delimeted file. TAB delimited file is shown in the image below. Datafile name is data.txt.

PROC IMPORT : TAB Delimited

DBMS=TAB tells SAS that the file being imported is a TAB delimited file.

PROC IMPORT DATAFILE='/home/deepanshu88us0/data.txt'
	DBMS=TAB
	OUT=WORK.READIN REPLACE;
	GETNAMES=YES;
RUN;

Another way to do this is by specifying DELIMITER='09'x and DBMS=DLM. '09'x represents the hexadecimal value for the tab character. The tab character (ASCII code 9) is commonly used as a delimiter in data files to separate columns (variables).

PROC IMPORT DATAFILE='/home/deepanshu88us0/data.txt'
	DBMS=DLM
	OUT=WORK.READIN REPLACE;
	GETNAMES=YES;
	DELIMITER='09'x;
RUN;

Use PROC IMPORT to import a file with multiple delimiters

While it is uncommon to see files with multiple delimiters, there are instances where certain vendors maintain files in complex formats that may include multiple delimiters. Suppose you have a raw file having both comma and tab as delimiters. In this case we need to specify both the delimiters in DELIMITER=','09'x '

PROC IMPORT DATAFILE='/home/deepanshu88us0/data.txt'
	DBMS=DLM
	OUT=WORK.READIN REPLACE;
	GETNAMES=YES;
	DELIMITER=','09'x ';
RUN;

Use PROC IMPORT to Import Excel File

Many businesses and organizations commonly work with MS Excel files for various purposes such as data storage, analysis, reporting etc. Hence MS Excel file formats are widely used in the real world. In the example below we are importing Excel file in SAS.

PROC IMPORT: Excel

DBMS=XLSX informs SAS that the file being imported is a MS Excel file (with .xlsx extension).

PROC IMPORT DATAFILE='/home/deepanshu88us0/Data.xlsx'
	DBMS=XLSX
	OUT=WORK.READIN REPLACE;
	GETNAMES=YES;
RUN;
Additional Options in PROC IMPORT to Import Excel File
PROC IMPORT DATAFILE="filename" 
DBMS=identifier
OUT=SASDataset REPLACE;
SHEET="sheetName";
GETNAMES=YES; 
DATAROW=N;
RANGE="rangeName";
RUN;
  • SHEET: Specify the name of the sheet in the Excel file from which you want to import data. When you use PROC IMPORT without explicitly mentioning the SHEET option, SAS will automatically import the first sheet of the Excel file by default. If you want to import a specific sheet, you need to explicitly specify the sheet name.
  • DATAROW: Specify the row number from which you want SAS to import data. If GETNAMES=YES, the DATAROW value must be greater than or equal to 2. If GETNAMES=NO, the DATAROW value must be greater than or equal to 1.
  • RANGE: Specify the range of Excel file. For e.g. RANGE="Sheet1$A1:D50"

To understand the above options in detail, checkout this tutorial -
Complete Guide : Importing Excel Data into SAS

Use PROC IMPORT to Import SPSS File

A few years ago, SPSS used to be the preferred statistical software for survey analysis. If you have a data file in SPSS format (.SAV) and you want to import it into SAS, you can refer to the SAS program below.

DBMS=SAV tells SAS that you are importing a file in the SPSS data file format.

PROC IMPORT DATAFILE='/home/deepanshu88us0/surveyData.sav'
	DBMS=SAV
	OUT=WORK.READIN REPLACE;
	fmtlib=WORK.FORMATS;
RUN;
The FMTLIB option tells SAS to create custom SAS formats if the data contains SPSS labels.
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 0 Response to "How to Use PROC IMPORT to Import Data into SAS"
Next → ← Prev