SAS How To's

2 Ways to Import a Text File into SAS (Examples!)

SAS Example Code

In this article, we discuss 2 ways to import a text file (.txt) into SAS.

We show how to import a text file with PROC IMPORT and a SAS DATA STEP. We support the methods with examples and SAS code.

Sample Data

Throughout the examples in this article, we import different .txt-files with data about a shoe company. This data is based on the SHOES dataset from the SASHELP library.

In a previous article, we discussed how to export a SAS dataset as a text file. Now, we will show how to import one.

The SHOES dataset has 395 rows and 7 columns. The image below shows the first 6 rows.

The next table shows the variable types, formats, and labels of the SHOES dataset. We want to make sure that the variable types haven’t changed after importing the text file.

Import a Text File into SAS with PROC IMPORT

The first method to import a text file into SAS is with the PROC IMPORT procedure. The PROC IMPORT procedure imports an external file and creates a SAS dataset. You can use PROC IMPORT to read different types of files, such as CSV, Excel, and Text.

How to Import a Text File with PROC IMPORT

In this section, we explain how to import a standard text file into SAS.

As an example, we will import the “shoes.txt“-file. This file contains 396 rows, including a header on the first row, and 7 columns. The values of the columns are separated by a tab.

This is how you import a text file into SAS with PROC IMPORT:

1. Define the location, name, and extension of the file

The first required argument of the PROC IMPORT procedure is the FILE=-argument (or DATAFILE=-argument), This argument specifies the full path and filename of the text file.

2.Specify the name of the SAS output dataset

The second required argument is the OUT=-argument. This argument specifies the name of the SAS dataset that the PROC IMPORT procedure creates.

3.Specify the type of data to import

The last required argument is the DBMS=-argument. This argument specifies the type of data PROC IMPORT is about to import. For text file (.txt) you use DBMS=tab.

The SAS code below shows how to combine these 3 arguments to import the “shoes.txt“-file.

proc import file="/folders/myfolders/import/shoes.txt"
    out=work.shoes
    dbms=tab;
run;

While running the code above, SAS writes some notes to the log. The notes indicate whether the import was successful and how many rows were read from the input file.

We use the PROC PRINT procedure to show the first 6 rows of the work.shoes dataset.

proc print data=work.shoes (obs=6) noobs;
run;

Finally, we use the PROC CONTENTS procedure to check the type of each column.

proc contents data=work.shoes order=varnum;
run;

It seems that the PROC IMPORT procedure has identified correctly the type of each variable. However, the DOLLAR12.-format of the last 3 columns has been lost. As an alternative, you can use a SAS Data Step to import a text file and explicitly specify the variable formats.

How to Overwrite an Existing Data Set

By default, the PROC IMPORT procedure does not overwrite existing SAS datasets. This way, SAS prevents you from losing valuable data. Moreover, SAS writes a note to the log to notify you.

For example, if you would run the SAS code below twice, SAS creates a special note the second time.

proc import file="/folders/myfolders/import/shoes.txt"
    out=work.shoes
    dbms=tab;
run;

However, on many occasions, you do want to overwrite an existing dataset. So, how do you replace a dataset with PROC IMPORT?

You can overwrite an existing dataset with the REPLACE option. This option must be placed after the DBMS=-argument.

For example:

proc import file="/folders/myfolders/import/shoes.txt"
    out=work.shoes
    dbms=tab
    replace;
run;

Import a Text File with Different Delimiters

When you import a text file and use the DBMS=tab argument, SAS assumes that the values of the columns are separated by a tab. However, there exist many other types of delimiters. For example, commas, semicolons, or pipes.

You can specify the delimiter of a text file with the DELIMITER=-option. The DELIMITER=-option is a new statement that must be placed after the REPLACE option. The character that is used as a delimiter must be written between quotes.

In this section, we show how to import text files with different delimiters.

How to Import a Comma Delimited Text File

A common character to separate the values in a text file is the comma.

These are the steps to import a comma delimited text file into SAS

  1. Start the PROC IMPORT procedure with the PROC IMPORT keywords.
  2. Define the location and filename of the text file with the FILE=-argument.
  3. Define the name of the SAS output dataset with the OUT=-argument.
  4. Use the DBMS=tab to import text files.
  5. Optionally, use the REPLACE option to overwrite an existing output dataset.
  6. Use the DELIMITER=“,”-argument to specify that the delimiter is a comma.
  7. Finish the PROC IMPORT prcodure with the RUN statement.

When you run the code below, SAS reads the comma-separated text file and creates a SAS dataset.

proc import file="/folders/myfolders/import/shoes_comma.txt"
    out=work.shoes
    dbms=tab
    replace;
    delimiter=",";
run;
 
proc print data=work.shoes (obs=6) noobs;
run;

As the image below shows, the values of the last 3 columns are character instead of numeric. This is because the values in these columns were written between double quotes in the input file.

You can change the format of the last 3 columns with the INPUT statement, or use a SAS Data Step to import the text file.

How to Import a Semicolon Delimited Text File

Another frequently used delimiter is the semicolon.

These are the steps to import a semicolon-delimited text file into SAS:

  1. Start the PROC IMPORT procedure with the PROC IMPORT keywords.
  2. Define the location and filename of the text file with the FILE=-argument.
  3. Define the name of the SAS output dataset with the OUT=-argument.
  4. Use the DBMS=tab to import text files.
  5. Optionally, use the REPLACE option to overwrite an existing output dataset.
  6. Use the DELIMITER=“;”-argument to specify that the delimiter is a semicolon.
  7. Finish the PROC IMPORT prcodure with the RUN statement.
proc import file="/folders/myfolders/import/shoes_semicolon.txt"
    out=work.shoes
    dbms=tab
    replace;
    delimiter=";";
run;
 
 
proc print data=work.shoes (obs=6) noobs;
run;

How to Import a Pipe Delimited Text File

A third delimiter is the pipe character (|). The image below shows an example of a pipe-delimited text file.

This is how to import a pipe-delimited text file into SAS with PROC IMPORT:

  1. Start the PROC IMPORT procedure with the PROC IMPORT keywords.
  2. Define the location and filename of the text file with the FILE=-argument.
  3. Define the name of the SAS output dataset with the OUT=-argument.
  4. Use the DBMS=tab to import text files.
  5. Optionally, use the REPLACE option to overwrite an existing output dataset.
  6. Use the DELIMITER=“|”-argument to specify that the delimiter is a pipe character.
  7. Finish the PROC IMPORT prcodure with the RUN statement.

The SAS code below contains an example of how to read a text file with the pipe character as the delimiter.

proc import file="/folders/myfolders/import/shoes_pipe.txt"
    out=work.shoes
    dbms=tab
    replace;
    delimiter="|";
run;
 
 
proc print data=work.shoes (obs=6) noobs;
run;

How to Import a Space Delimited Text File

The last character we discuss as a delimiter is a single space/blank.

The SAS code to read text files with a blank as a delimiter is straightforward. However, before you start reading a space-delimited file, make sure that data values that contain spaces are written between double-quotes. See the image below for some examples.

These are the 7 steps to import a space/blank delimited text file into SAS:

  1. Start the PROC IMPORT procedure with the PROC IMPORT keywords.
  2. Define the location and filename of the text file with the FILE=-argument.
  3. Define the name of the SAS output dataset with the OUT=-argument.
  4. Use the DBMS=tab to import text files.
  5. Optionally, use the REPLACE option to overwrite an existing output dataset.
  6. Use the DELIMITER=” “-argument to specify that the delimiter is a signle space/blank.
  7. Finish the PROC IMPORT prcodure with the RUN statement.
proc import file="/folders/myfolders/import/shoes_space.txt"
    out=work.shoes
    dbms=tab
    replace;
    delimiter=" ";
run;
 
 
proc print data=work.shoes (obs=6) noobs;
run;

How to Import a Text File without Header

The PROC IMPORT procedure expects that the first row of a text file contains the header. But, how do you import a text file without a header into SAS?

For example, the text file below has no header and the data values start directly on the first row.

SAS assumes that the first row contains the column names if you would use the standard code to read the file above. Since this is not the case, the result of the import is not as expected.

proc import file="/folders/myfolders/import/shoes_no_header.txt"
    out=work.shoes
    dbms=tab
    replace;
run;
 
proc print data=work.shoes (obs=6) noobs;
run;

To import a text file without a header, you need the GETNAMES=-option. This option lets the PROC IMPORT procedure know that the text file has column names or not.

By default, the value of the GETNAMES=-option is YES. However, if the input file doesn’t have a header, you need to use GETNAMES=NO.

The code below shows how to use the GETNAMES=-option.

proc import file="/folders/myfolders/import/shoes_no_header.txt"
    out=work.shoes
    dbms=tab
    replace;
    getnames=no;
run;
 
proc print data=work.shoes (obs=6) noobs;
run;

As you can see in the image below, the import was successful. If a text file does not contain a header, SAS calls the columns VAR1, VAR2, VAR3, etc. If necessary, you can change the column names with the RENAME=-option in a subsequent Data Step.

How to Import a Text File and Specify the First Row with Data

Normally, the first row of a file contains data. But, how do you import a text file where the data starts at another row?

For example, in the text file below, the data starts at row 4.

If you would run the standard SAS code to import the file above, SAS assumes that the first row contains the header and subsequent rows have data values. However, since this is not the case, the result of the PROC IMPORT procedure is not as expected.

proc import file="/folders/myfolders/import/shoes_startrow.txt"
    out=work.shoes
    dbms=tab
    replace;
run;
 
proc print data=work.shoes (obs=6) noobs;
run;

A solution to the problem above is to add an extra Data Step to filter out the blank rows and change the column names. However, there is an easier way to import text files where the data doesn’t start at the first row.

You can use the DATAROW=-option to start the import of a text file from a specified row. The DATAROW=-option is a separate statement of the PROC IMPORT procedure and must be placed after the REPLACE option. Acceptable values of the DATAROW=-option are all integers greater than or equal to 1.

The SAS code below shows how to read a text file where the data starts in the fourth row.

proc import file="/folders/myfolders/import/shoes_startrow.txt"
    out=work.shoes
    dbms=tab
    replace;
    datarow=4;
run;
 
proc print data=work.shoes (obs=6) noobs;
run;

Note that, if you use the DATAROW=-option, the GETNAMES=-option won’t work. The GETNAMES=-option only works if the variable names are in the first row.

How to Import a Text File and Guess the Variable Types

The last option of the PROC IMPORT procedure we discuss is the GUESSINGROWS=-option.

Because PROC IMPORT doesn’t let you specify the data type of the variables of a text file, it makes a guess based on the first 20 rows.

However, if the first 20 rows are empty or can be interpreted as both numeric and character, then this guess might be wrong. So, to increment the number of rows that SAS takes into account to guess the data type you can use the GUESSINGROWS=-option. You can specify the number of rows or set it to MAX (2147483647 rows).

The example below shows how to use the first 100 rows to guess the variable type of each column.

proc import file="/folders/myfolders/import/shoes.txt"
    out=work.shoes
    dbms=tab
    replace;
    guessingrows=100;
run;
 
proc print data=work.shoes (obs=6) noobs;
run;

Import a Text File into SAS with a DATA STEP

A second method to import a text file into SAS is with a SAS DATA Step.

This method requires more lines of code compared to the PROC IMPORT procedure. However, the SAS DATA Step provides a lot more flexibility concerning defining variable types and variable formats.

How to Import a Text File with a DATA STEP

The image below shows the first rows of the “shoes.txt“-file that we will import with a SAS DATA Step.

Here is how to import a text file into SAS with a SAS DATA Step:

1. Specify the output dataset

The first step in reading a text file with a DATA STEP into SAS is specifying the name of the dataset that will contain the imported data. You do this with the DATA statement. For example,

DATA work.shoes_import;

2. Define the file location, file name, and file extension of the text file

The second step is to specify the full path and filename of the text file you want to import. You do this with the INFILE statement. The path and filename must be written between quotes.

infile "/folders/myfolders/import/shoes.txt"

3. Specify the INFILE options

The INFILE statement has several options.

The delimiter option specifies the symbol that separates the values in the text file. Normally, this is a tab, but it can be another character. The delimiter must be enclosed in double-quotes. The delimiter for a tab-separated fil is “09”x.

The missover option tells SAS to continue reading the text file when it encounters a missing value. That is to say when it encounters two consecutive delimiters.

The dsd option lets SAS know to ignore delimiters that are enclosed between double-quotes. For example (in the delimiter is a comma), “$15,500”.

The firstobs option specifies the first row with data. If you use the DATA Step to import a text file, SAS doesn’t expect a header. However, if the file does have a header and the actual data starts on the second row, then you must set the FIRSTOBS=-option to 2.

4. Define the formats of the variables in the text file

The fourth step is to define the names and formats of the variables in the text file. You do this with the INFORMAT statement. The INFORMAT statement lets SAS know how to interpret the values (i.e., their type and format).

5. Define the formats of the variables in the output dataset

The last step is to define the names and formats of the variables in the output dataset. You do this with the FORMAT statement. In general, the INFORMAT and FORMAT statements are similar.

The example below shows how to import a tab-delimited text file with a SAS DATA Step. Note that to read a tab-delimited file, you must use DELIMITER=“09”x.

data work.shoes;
    infile "/folders/myfolders/import/shoes.txt"
    delimiter = "09"x
    missover 
    dsd
    firstobs=2;
 
    informat Region $50.;
    informat Product $50.;
    informat Subsidiary $50.;
    informat Stores best12.;
    informat Sales dollar12.;
    informat Inventory dollar12.;
    informat Returns dollar12.;
 
    format Region $50.;
    format Product $50.;
    format Subsidiary $50.;
    format Stores best12.;
    format Sales dollar12.;
    format Inventory dollar12.;
    format Returns dollar12.;
 
    input
    Region $
    Product $
    Subsidiary $
    Stores
    Sales
    Inventory
    Returns;
run;
 
proc print data=work.shoes (obs=6) noobs;
run;

In contrast to PROC IMPORT, the SAS DATA Step automatically overwrites existing output datasets. So, it is not necessary to use the REPLACE option.

As mentioned earlier, using a SAS DATA Step to import a text file requires many lines of code. To make life easier, you could use PROC IMPORT first, go to the log, copy the automatically generated code, and modify it as needed.

For example, this is the code generated by the PROC IMPORT procedure.

Import a Fixed Width Text File with Column Input

The data values in a text file can be separated by a special character, such as a comma or a semicolon. But, how do you import text files with a fixed width between the columns?

The image below shows an example of a text file where the values of each variable start at a specific position (i.e., column).

You import a fixed-width text file into SAS with a DATA Step. You use the Column Input method to specify the name, type, and column in which the value of each variable is located.

The Column Input method is a special type of INPUT statement. The statement starts with the INPUT keyword followed by the variable names, their types, and the position of the data values.

Note that, the Column Input method is also capable of reading fixed-width text files with missing values.

The SAS code below shows an example of how to read a text file where the data values start at a specific column.

data work.shoes;
    infile "/folders/myfolders/import/shoes_fixed_width.txt"
    delimiter = "09"x
    missover 
    dsd
    firstobs=2;
 
    informat Region $50.;
    informat Product $50.;
    informat Subsidiary $50.;
    informat Stores best12.;
    informat Sales $50.;
    informat Inventory $50.;
    informat Returns $50.;
 
    format Region $50.;
    format Product $50.;
    format Subsidiary $50.;
    format Stores best12.;
    format Sales $50.;
    format Inventory $50.;
    format Returns $50.;
 
    input
    Region $ 1-29
    Product $ 30-49
    Subsidiary $ 50-69
    Stores 70-74
    Sales $ 75-84
    Inventory $ 85-94
    Returns $;
run;

Similar Posts