It is common practice to manipulate your data in SAS and export it for further analysis. SAS provides a variety of formats to export your data. A common format is a Text file (.TXT). But, how do you export your SAS dataset as a TXT file?
You export a SAS dataset as a TXT file with PROC EXPORT. You provide the EXPORT procedure with the data you want to export, the desired output location, and SAS will create the Text file. PROC EXPORT has options to change the delimiter, print column labels, and remove the header.
In this article, we use a sample dataset that we will export as a TXT file (work.my_data). This dataset is based on the CARS dataset from the SASHELP library, but with fewer columns.
Do You Know? – How to Remove Columns with the DROP=-option
Export a SAS Dataset as TXT File with PROC EXPORT
The EXPORT Procedure
The easiest way to export a SAS dataset as a TXT file is with PROC EXPORT. The EXPORT procedure is a flexible procedure that can be used to export data in many formats such as Excel (.xlsx), Comma Separated Values format (.csv), and Text (.txt). It also provides options to export data with different delimiters, without a header, or export variable labels instead of variable names.
Syntax of PROC EXPORT
To export data from SAS as a TXT file with PROC EXPORT you need to define at least three parameters:
- DATA=-option to specify the SAS dataset you want to export. For example, DATA=work.my_data.
- OUTFILE=-option to define the output location, the file name, and the file extension. For instance, OUTFILE=”/folders/myfolders/export/cars.txt”
- DBMS=-option to specify the file extension. For example, DBMS=tab for text files.
The DATA=-option specifies the SAS dataset you want to export. You can use either a one- or two-level SAS name (i.e., with or without libref). When you export a dataset as a TXT file, you can use dataset options such as KEEP, RENAME, and WHERE in the DATA=-option.
The OUTFILE=-option specifies the location, file name, and the file extension of the output file. Keep in mind that the complete path (location and file name) can’t exceed 201 characters.
The DBMS=-option specifies the type of file the EXPORT procedure creates (e.g., .txt). To create a TXT file, you should use the DBMS=tab option.
The SAS code below exports the work.my_data dataset as a TXT file and creates a file called cars.txt.
proc export data=work.my_data outfile="/folders/myfolders/export/cars.txt" dbms=tab; run;
As you can see, a (default) text file is tab-delimited. Later in this article, we will show how to change the default delimiter.
How to Replace an Existing TXT File
If you want to overwrite an existing TXT file, the EXPORT procedure, by default, won’t do this. The EXPORT procedure protects the existing file and doesn’t permit you to replace it.
Moreover, SAS writes a note to the log that it cancelled the export.
NOTE: Export cancelled. Output file XYZ already exists. Specify REPLACE option to overwrite.
However, you can change this default behavior. So, how do you overwrite an existing TXT file with PROC EXPORT?
You use the REPLACE option to overwrite an existing TXT file with PROC EXPORT. The REPLACE option is part of the EXPORT statement and needs to be placed after the DBMS=-option.
In the example below, we replace the cars.txt file.
proc export data=work.my_data outfile="/folders/myfolders/export/cars.txt" dbms=tab replace; run;
Keep in mind that SAS won’t ask for confirmation to replace the existing file. So, once you execute the code, the original file is gone.
How to Change the Delimiter of the TXT File
As we have seen above, SAS creates by default tab-delimited TXT files. However, for some purposes, it is necessary to change the default separator (i.e., the delimiter). So, how can you change the delimiter of SAS’ PROC EXPORT?
You use the DELIMITER=-statement to change the default separator of PROC EXPORT. The delimiter must be placed between quotation marks. Typical delimiters are the semicolon, the forward-slash, or a comma. The DLM=-statement is a synonym of the DELIMITER=-statement and can be used instead.
The SAS code below shows how to change the delimiter to a semi-colon.
proc export data=work.my_data outfile="/folders/myfolders/export/cars.txt" dbms=tab replace; delimiter=";"; run;
How to Export a TXT file with the Header
Usually, you need the header (i.e., column names) in your output file. That is why SAS includes them by default in all exported files. However, on some occasions, you don’t want the column names in the output file. So, how do you exclude the header from the TXT file?
The PUTNAMES=-statement allows you to exclude the column names from the TXT file. This statement has two options, namely Yes (default) and No. So, to omit the header, you use PUTNAMES=NO.
In the example below, we create a TXT file without column names.
proc export data=work.my_data outfile="/folders/myfolders/export/cars.txt" dbms=tab replace; putnames=NO; run;
How to Export Column Labels instead of Column Names
Finally, SAS datasets have column names and, optionally, column labels. Column labels can contain special characters such as blanks and percentage signs. However, by default, PROC EXPORT exports the column names instead of the column labels. So, how do you export the column labels with PROC EXPORT?
You use the LABEL option to create a TXT file with the column labels instead of the column names. You place this option after the REPLACE option. If a column doesn’t have a label, then PROC EXPORT uses the column name.
For example, with the SAS code below we export the column labels.
proc export data=work.my_data outfile="/folders/myfolders/export/cars.txt" dbms=tab label replace; run;
Keep in mind that column labels are exported between double quotes.
With a PROC CONTENTS statement, you can check whether a column has a label. For instance, some of the columns of our sample data work.my_data don’t have labels.
Do you know? How to List Column Names and Column Labels
Summary: Export a SAS Dataset as a TXT File
In this article, we discussed how to use PROC EXPORT to generate a TXT file. To summarize, you need to follow these steps to create a TXT file from a SAS dataset:
- Start the PROC EXPORT procedure.
- Use the DATA=-option to specify your SAS dataset.
- Use the OUTFILE=-option to define the location of the TXT file.
- Apply the DBMS=tab option for text files.
- (Optional) Use the REPLACE option to overwrite existing files.
- (Optional) Change the delimiter with the DELIMITER=-option.
- (Optional) Remove column headers with PUTNAMES = NO.
- (Optional) Output column labels with the LABELS option.
- Finish the EXPORT procedure with the RUN statement.
Looking for more useful “How To’s”? Check out this page