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.
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
- DATAFILE: Specify the location of the file to be imported.
- OUT: Specify the name to assign to the dataset after it is imported into SAS
- DBMS: Define the format of the file being imported. Some of the common values are CSV, EXCEL, TAB, DLM, ACCESS.
- REPLACE: Determine whether to replace the existing SAS Dataset. Yes/No.
- 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.
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 |
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.
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.
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;
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.
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.
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.
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;
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.
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.
Share Share Tweet