One of the most common data types to import into SAS are comma separated values (CSV) files. As the name implies, the values (columns) are separated by commas, and usually have the file extension “.csv”.
This article will provide a walkthrough of 3 different methods for importing CSV files into SAS, including:
- PROC IMPORT
- Data Step
- SAS Studio Point-and-click
cars.csv - download
Before running any of the examples below, you will need to replace the path ‘/home/your_username/SASCrunch’ with a directory that you have read/write access to in your environment.
This can vary depending on the machine you are running SAS on, the version of SAS you are running and the Operating System (OS) you are using.
If you are using SAS OnDemand for Academics, you must first upload the files to the SAS server.
Please visit here for instruction if you are not sure how to do this.
1. Importing a Comma Separated Values (CSV) File with PROC IMPORT
Using the cars.csv dataset, we will walk though an example of how to import this dataset into SAS using PROC IMPORT.
The first part you need following the PROC IMPORT statement is the datafile argument. The datafile argument is required so that SAS knows where the file you would like to import is stored and what the name of that file is.
Inside the quotation marks following the datafile argument, you need to add the complete path, including the filename and file extension.
As noted above, be sure to replace ‘/home/your_username/SASCrunch’ with the correct directory on your machine or environment where cars.csv is saved.
In this example, “/home/your_username/SASCrunch” is the path, “cars” is the filename, and “.csv” is the file extension.
After specifying the location and dataset name, you can add an output dataset name using the out argument. Here, a dataset named CARS is going to be output to the WORK directory.
Finally, the DBMS option is used to indicate the type of file that you would like to import. In this case, the value for DBMS is CSV.
proc import datafile = '/home/your_username/SASCrunch/cars.csv'
out = work.cars
dbms = CSV
After running the above code (with the datafile path modified to point to a folder in your environment) you should see the output data (shown partially below) with 428 rows and 15 columns:
proc import datafile = '/home/your_username/SASCrunch/cars.csv'
out = cars
dbms = csv
Do you have a hard time learning SAS?
Take our Practical SAS Training Course for Absolute Beginners and learn how to write your first SAS program!
2. Importing a Comma Separated Values (CSV) File with Data Step
Although the amount of SAS code required to import a CSV file using Data Step is longer than the code required for PROC IMPORT, using Data Step code allows for greater flexibility.
By using Data Step code, the variable names, lengths and types can be manually specified at the time of import. The advantage is that this allows you to format the dataset exactly the way you desire as soon as it is created in SAS, rather than having to make additional modifications later on.
First, as with any SAS Data Step code, you need to specify the name and location for the dataset you are going to create. Here, a dataset named CARS_DATASTEP will be created in the WORK directory.
The next step is to use the INFILE statement. The INFILE statement in this case is made up of 6 components:
- The location of the CSV file - /home/your_username/SASCrunch in this example
- Delimiter option – the delimiter found on the input file enclosed in quotation marks (delimiter is ‘,’ in this example since it is a CSV file)
- MISSOVER option – Tells SAS to keep reading the record even if a missing value is found for one of the variables
- FIRSTOBS – The first row that contains the observations in the input file (Set to 2 in this example since the observations start on the second row in the CARS.CSV file)
- DSD – Tells SAS that when a delimiter is found within a quotation mark in the dataset, it should be treated as a value and not a delimiter
- LRECL – Maximum length for an entire record (32767 is the default maximum to use which will ensure no truncation within 32767 characters)
After the INFILE statement, the simplest way to ensure that your variable names, lengths, types and formats are specified correctly is to use a format statement for each variable. After an appropriate format has been assigned to each variable, the variables that you would like to import should be listed in order after an INPUT statement. Note that character variables should have a dollar sign ($) after each variable name.
Note that you can also specify INFORMATs and LENGTHs optionally here, but in most cases the FORMAT and INPUT statements should be all you need for a successful import.
Below is the Data Step code that would successfully import the CARS.CSV file into a SAS dataset. As mentioned, be sure to update the path to the correct location of the CARS.CSV file on your environment before running the following code:
lrecl = 32767;
format Make $5. ;
format Model $30. ;
format Type $6. ;
format Origin $6. ;
format DriveTrain $5. ;
format MSRP $9. ;
format Invoice $9. ;
format EngineSize best12. ;
format Cylinders best12. ;
format Horsepower best12. ;
format MPG_City best12. ;
format MPG_Highway best12. ;
format Weight best12. ;
format Wheelbase best12. ;
format Length best12. ;
3. Importing a Comma Separated Values (CSV) File with SAS Studio
Using the built-in import data tool inside of SAS studio, it is possible to import CSV files into SAS without actually writing any code. The same options that you specified in PROC IMPORT can be customized using the point and click utility within SAS studio.
This section will also show you how the tool can be used to generate the PROC IMPORT syntax that you can later modify and run on your own.
Here is how you can use the point-and-click tool to import a CSV file into SAS.
1. Click on the Server Files and Folders Pane on the left hand side of the screen:
3. Right click on the file which you would like to import and select Import Data:
4. Click on the Settings tab and review the current settings:
By default, SAS will try to choose the most appropriate options. However, similar to PROC IMPORT, you can change the file type, starting row to read the data from, or the GUESSINGROWS option (i.e. the number of rows that SAS should read before determining the optimal variable types and lengths).
In this example, we will use the default Options, but will change the name of the output dataset to something more informative:
5. To change the name of the output dataset, click Change:
Become a Certified SAS Specialist
Get access to two SAS base certification prep courses and 150+ practice exercises
6. Type in the data set name cars_studio and click Save:
7. For convenience, SAS also generates and displays the PROC IMPORT syntax which will be used to execute the import.
If you prefer, you can modify the import settings using the generated syntax as a starting point. You can also copy, paste and modify this code as needed. To see the PROC IMPORT syntax SAS generates, simply click the Code/Results tab:
Note that the new Data Set name specified in the Settings window has also been updated in the Code tab automatically to reflect this change.
8. The Split tab also provides a convenient view of both the Settings point-and-click window and the corresponding generated code window, all in one screen:
9. Once you have finished reviewing the settings and making any necessary changes, click the Run button to complete the import.
Note that no custom settings are required in this example to import the CARS.CSV file into a SAS dataset.
10. After running the import utility, you can click on the Code/Results tab again to explore the contents and view the newly created dataset (WORK.CARS_STUDIO):