CHAPTER  10

Exporting Your Data

10.1    Methods for Exporting Your Data

10.2    Writing Delimited Files with the EXPORT Procedure

10.3    Writing Delimited Files Using ODS

10.4    Writing Microsoft Excel Files with the EXPORT Procedure

10.5    Writing Microsoft Excel Files Using ODS

10.6    Writing Raw Data Files with the DATA Step

10.1   Methods for Exporting Your Data

In our ever increasingly complex world, people often need to transfer data from one application to another. Fortunately, SAS gives you many options for doing this. The types of files that you can create and the methods available for creating those files depend on how you run SAS, what operating environment you are using, and whether you have SAS/ACCESS software.

 

Methods for exporting data to other applications fall into these general categories:

♦      creating delimited or text files that the other software can read

♦      creating files in formats like HTML or XML that the other software can read

♦      writing the data in the other software’s native format

Creating delimited and text files  No matter what your environment, you can always create delimited or text files and most software has the ability to read these types of data files.

♦      The DATA step, discussed in Section 10.6, gives you the most control over the format of your files, but requires the most steps.

♦      The EXPORT procedure, discussed in Section 10.2, is easy to use, but you have less control over the result.

♦      SAS Studio, SAS Enterprise Guide, and the SAS windowing environment each have point-and-click methods for creating delimited files from SAS data sets. These methods are not covered in this book, but with a little exploration you will find various menus, icons, or tasks for exporting data.

♦      The Output Delivery System (ODS), discussed in Section 10.3, can create comma-separated values (CSV) files from any procedure output, and a simple PROC PRINT will produce a reasonable file for importing data into other programs.

Creating HTML and XML files  Using ODS, you can create HTML and XML files from any procedure output. Many applications can read data in these types of files. Although we do not cover creating HTML and XML files in this chapter, the general method is the same as creating CSV files. Creating HTML files is discussed in Section 5.2.

Creating files in native formats  There are several methods for creating files in the native format of other software applications. Not all methods are available for all software applications, and some methods depend on what SAS software products you have installed, which operating environment, and what version of SAS you are using. We do not attempt to cover all methods, so see the SAS Documentation for complete information.

♦      The EXPORT procedure, discussed in Section 10.4, can create PC files in many formats including Microsoft Excel, Microsoft Access, dBase, Paradox, SPSS, Stata, and JMP. However, for most of these formats you need SAS/ACCESS Interface to PC Files. Support for JMP files is now included in Base SAS. This method is available only for the Windows or UNIX operating systems.

♦      The EXPORT procedure can also create PC files via the PC Files Server. You may have a PC Files Server application running in your SAS environment. The PC Files Server facilitates sharing of some PC files between different Windows computers, between Windows and UNIX computers, or even on a single Windows computer. The PC Files Server application requires that SAS/ACCESS Interface to PC Files be installed. This method is not covered in this book.

♦      SAS Studio, SAS Enterprise Guide, and the SAS windowing environment each have point-and-click methods for creating several native file formats from SAS data sets. For most formats you need SAS/ACCESS Interface to PC Files. These methods are not covered in this book, but with a little exploration you will find various menus, icons, or tasks for exporting data.

♦      The Output Delivery System(ODS), discussed in Section 10.5, allows you to create Microsoft Excel files using the ODS EXCEL destination. The ODS EXCEL destination gives you more control over the appearance of the Excel file than other exporting methods discussed here.

♦      With the XLSX LIBNAME engine, discussed in Section 2.5, you can read from and write to an Excel file as if it were a SAS data set. This engine works for files created by any version of Microsoft Excel 2007 or later on the Windows or UNIX operating environments. You must have SAS 9.4M2 or higher and SAS/ACCESS Interface to PC Files software.

♦      For database management systems like ORACLE, DB2, INGRES, MYSQL, and Sybase, there are SAS/ACCESS products that allow you to create files in the native formats of these applications. These products are not discussed in this book.

10.2    Writing Delimited Files with the EXPORT Procedure

If you want to create a generic data file that most software can read, then creating a delimited text file may be the way to go. The EXPORT procedure allows you to create data files with any delimiter you choose. The resulting text file will have one delimiter between each data value, and each observation will occupy one line of text.

The EXPORT procedure  The general form of PROC EXPORT is:

PROC EXPORT DATA = data-set OUTFILE = 'filename' REPLACE;

where data-set is the SAS data set you want to export, and filename is the name you make up for the output data file. The REPLACE option tells SAS to replace the file if it already exists. The following statement tells SAS to read a temporary SAS data set named HOTELS and write a comma-delimited file named Hotels.csv in a directory named MyRawData on the C drive (Windows):

PROC EXPORT DATA = hotels OUTFILE = 'c:MyRawDataHotels.csv' REPLACE;

RUN;

SAS uses the last part of the filename, called the file extension, to decide what type of file to create. You can also specify the file type by adding the DBMS= option to the PROC EXPORT statement. The following table shows the filename extensions and DBMS identifiers that are available for delimited files. If you specify the DBMS= option, then it takes precedence over the file extension.

Type of file	Extension	DBMS Identifier
Comma-delimited	.csv	CSV
Tab-delimited	.txt	TAB
Space-delimited		DLM

Notice that for space-delimited files, there is no standard extension so you must use the DBMS= option. The following statement, containing the DBMS= option, tells SAS to create a space-delimited file named Hotels.dat.

PROC EXPORT DATA = hotels OUTFILE = 'c:MyRawDataHotels.dat'
        DBMS = DLM REPLACE;

RUN;

If you want to create a file with a delimiter other than a comma, tab, or space, then you can add the DELIMITER statement. If you use the DELIMITER statement, then it does not matter which  file extension you use, or which DBMS identifier you specify, the file will have the delimiter that you specify in the DELIMITER statement. For example, the following would produce a file, Hotels.txt, that has the ampersand (&) as the delimiter:

PROC EXPORT DATA = hotels OUTFILE = 'c:MyRawDataHotels.txt'
        DBMS = DLM REPLACE;

   DELIMITER='&';

RUN;

Example  A travel company has the following tab-delimited file containing information about golf courses in Hawaii. For each golf course the file includes its name, number of holes, par, yardage, and greens fees.

CourseName            Holes    Par     Yardage      GreenFees

Pukalani              18       72      6945         89.00

Kahili                18       72      6570         119.00

Maui Nui Blue         18       71      6404         69.00

Waiehu Municipal      18       72      6330         63.00

Wailea Gold           18       72                   250.00

Dunes at Maui Lani    18       72      6841         105.00

Kaanapali             18       71      6555         144.00

The following program uses the IMPORT procedure to read the data file and create a permanent SAS data set named GOLF in the MySASLib directory on the C drive (Windows).

LIBNAME travel 'c:MySASLib';

PROC IMPORT DATAFILE = 'c:MyRawDataGolf.txt' OUT = travel.golf REPLACE;

RUN;

Now, suppose that a customer wants the data in a comma-delimited file so they can import the data into their software. The following program writes a plain text, comma-delimited file:

LIBNAME travel 'c:MySASLib';

*Create comma-delimited file;

PROC EXPORT DATA = travel.golf OUTFILE = 'c:MyRawDataGolf.csv' REPLACE;

RUN;

Because the name of the output file ends with .csv and there is no DELIMITER statement, SAS will write a comma-delimited file. If you run this program, your log will contain the following notes:

Notice that while the SAS data set contained seven observations, SAS wrote eight records. The extra record contains the variable names. Here is the comma-delimited text file created by the EXPORT procedure.

CourseName,Holes,Par,Yardage,GreenFees

Pukalani,18,72,6945,89

Kahili,18,72,6570,119

Maui Nui Blue,18,71,6404,69

Waiehu Municipal,18,72,6330,63

Wailea Gold,18,72,,250

Dunes at Maui Lani,18,72,6841,105

Kaanapali,18,71,6555,144

Any format that you have assigned to variables in the SAS data set will be used by PROC EXPORT when creating the delimited file. If you want to change a format, use a FORMAT statement (discussed in Section 4.6) in a DATA step before running PROC EXPORT.

10.3   Writing Delimited Files Using ODS

ODS is a powerful tool for creating all sorts of output formats. Some of the output formats that ODS can create are useful for transferring data and results from SAS to other applications. Many applications can read data that are in comma-separated values (CSV) format, and the great thing is that you can use this method in any operating environment and it’s included in Base SAS.

You can use ODS CSV to export data from most procedures, and PROC PRINT is a good way to get a file with a listing of your data. By default, SAS will insert a period for any missing numeric data. If you would rather have SAS print nothing for missing numeric data, then you can use the MISSING='  ' system option. Also, by default, PROC PRINT includes observation numbers. If you don’t want observation numbers in your output file, then use the NOOBS option in the PROC PRINT statement.

The CSV destination puts commas between data values and double quotation marks around character values. The double quotation marks allow data values to contain commas. To create a CSV file containing your data, use the following ODS and PROC PRINT statements:

OPTIONS MISSING = '';

ODS CSV FILE = 'filename.csv';

PROC PRINT DATA = data-set-name NOOBS;

RUN;

ODS CSV CLOSE;

where filename.csv is the name of the CSV file that you are creating, and data-set-name is the name of the SAS data set you want to export. The CSV output destination does not include titles or footnotes. If you want titles and footnotes to appear in the CSV file, then use the CSVALL output destination instead of CSV.

Example  This example uses the permanent SAS data set, GOLF (created in the previous section), which has information about golf courses in Hawaii.

	CourseName	Holes	Par	Yardage	GreenFees
1	Pukalani	18	72	6945	89
2	Kahili	18	72	6570	119
3	Maui Nui Blue	18	71	6404	69
4	Waiehu Municipal	18	72	6330	63
5	Wailea Gold	18	72	.	250
6	Dunes at Maui Lani	18	72	6841	105
7	Kaanapali	18	71	6555	144

 

The following program uses ODS to create two CSV files. The first file, GolfInfo.csv, contains the results of PROC PRINT which gives a listing of the data. The second file, GolfMeans.csv, captures the results of a PROC MEANS.

LIBNAME travel 'c:MySASLib';

*Create comma-delimited file from PROC PRINT results;

OPTIONS MISSING = '';

ODS CSV FILE = 'c:MyRawDataGolfInfo.csv';

PROC PRINT DATA = travel.golf NOOBS;

RUN;

ODS CSV CLOSE;

 

*Create comma-delimted file from PROC MEANS results;

ODS CSV FILE='c:MyRawDataGolfMeans.csv';

PROC MEANS DATA = travel.golf MAXDEC = 0 MEAN MAX;

  CLASS par;

  VAR Yardage GreenFees;

RUN;

ODS CSV CLOSE;

Here is the comma-delimited text file, GolfInfo.csv, created from the PROC PRINT:

"CourseName","Holes","Par","Yardage","GreenFees"

"Pukalani",18,72,6945,89

"Kahili",18,72,6570,119

"Maui Nui Blue",18,71,6404,69

"Waiehu Municipal",18,72,6330,63

"Wailea Gold",18,72,,250

"Dunes at Maui Lani",18,72,6841,105

"Kaanapali",18,71,6555,144

Here is the comma delimited text file, GolfMeans.csv, created from the PROC MEANS:

"Par","N Obs","Variable","Mean","Maximum","Variable","Mean","Maximum"

"71","2","Yardage",6480,6555,"GreenFees",107,144

"72","5","Yardage",6672,6945,"GreenFees",125,250

The following notes appear in the SAS log showing that seven observations were read from the GOLF data set, and both CSV files were successfully written:

NOTE: There were 7 observations read from the data set TRAVEL.GOLF.

NOTE: Writing CSV Body file: c:MyRawDataGolfMeans.csv

 

10.4   Writing Microsoft Excel Files with the EXPORT Procedure

If you are using the Windows or UNIX operating environment, and you have SAS/ACCESS Interface to PC Files, then you can use the EXPORT procedure to create Microsoft Excel files.  

Here is the general form of PROC EXPORT for writing Excel files:

PROC EXPORT DATA = data-set OUTFILE = 'filename' DBMS = identifier

            REPLACE;

where data-set is the SAS data set that you want to export, and filename is the name that you make up for the output data file. The DBMS= option tells SAS what type of Excel file to create. The REPLACE option tells SAS to replace the file if it already exists.

DBMS identifiers  There are several DBMS identifiers you can use to create Excel files. Three commonly used identifiers are EXCEL, XLS, and XLSX. The EXCEL identifier is available only on Windows. The XLS identifier creates older style files (.xls extension) and is available on Windows and UNIX. The XLSX identifier creates newer style files (.xlsx extension) and is available on both Windows and UNIX. Not all of these identifiers may work for you if your Windows computer has a mixture of 64-bit and 32-bit applications. In addition, some computer configurations may require that a PC Files Server be installed. The PC Files Server uses the EXCELCS identifier. See the SAS Documentation for more information. The following statement, containing the DBMS= option, tells SAS to create an Excel file named Hotels.xlsx.

PROC EXPORT DATA = hotels OUTFILE = 'c:MyExcelHotels.xlsx'
        DBMS = XLSX REPLACE;

RUN;

Naming sheets  By default, the name of the Microsoft Excel sheet will be the same as the name of the SAS data set.  If you want the sheet to have a different name, then specify it in the SHEET= statement. Special characters in sheet names will be converted to underscores. The following statement creates a sheet named Golf_Hotels:

SHEET = 'Golf/Hotels';

You can create Excel files with multiple sheets by submitting multiple EXPORT procedures, specifying the same filename in the OUTFILE= option, but using a different name in the SHEET= statement. If a sheet by that name already exists in the file, it will not be overwritten unless you also specify the REPLACE option.

 

Example  This example uses the SAS data set created in Section 10.2, which contains information about Hawaiian golf courses.

	CourseName	Holes	Par	Yardage	GreenFees
1	Pukalani	18	72	6945	89
2	Kahili	18	72	6570	119
3	Maui Nui Blue	18	71	6404	69
4	Waiehu Municipal	18	72	6330	63
5	Wailea Gold	18	72	.	250
6	Dunes at Maui Lani	18	72	6841	105
7	Kaanapali	18	71	6555	144

Suppose that your office mate needs that information, but she wants it in a Microsoft Excel file.  The following program writes a Microsoft Excel file from the SAS data set GOLF:

LIBNAME travel 'c:MySASLib';

* Create Microsoft Excel file';

PROC EXPORT DATA = travel.golf OUTFILE = 'c:MyExcelGolf.xlsx'

   DBMS = XLSX REPLACE;

RUN;

Here is what the Microsoft Excel file looks like. Notice that the name of the sheet is the same as the name of the SAS data set.

If you have user-defined formats that have been associated with variables, only the unformatted values will be exported to Excel. The FORMAT statement is not supported in the EXPORT procedure.

If you run this program, your log will contain the following notes.

 

10.5   Writing Microsoft Excel Files Using ODS

In Section 10.3 we showed how to create comma-separated values (CSV) files using ODS. You can read CSV files in Microsoft Excel, but using the ODS EXCEL destination you can create native Excel files, and you have a lot of control over what those files look like in Excel. The EXCEL destination is included in Base SAS and can be used in any operating environment (for z/OS it works only under HFS file systems).

You can use ODS EXCEL to export data from most procedures, and PROC PRINT is a good way to get a file with a listing of your data. By default, SAS will insert a period for any missing numeric data. If you would rather have nothing for missing numeric data, then use the MISSING='  ' system option. Also, by default, PROC PRINT includes observation numbers. If you don’t want observation numbers in your output file, then use the NOOBS option in the PROC PRINT statement. To create an Excel file containing your data, use the following ODS and PROC PRINT statements:

OPTIONS MISSING = ' ';

ODS EXCEL FILE = 'filename.xlsx';

PROC PRINT DATA = data-set-name NOOBS;

RUN;

ODS EXCEL CLOSE;

where filename.xlsx is the name of the Excel file that you are creating. To create an Excel file from other procedure results, insert the desired PROC statements instead of the PROC PRINT. If you want, you can specify a style for your Excel file, by adding the STYLE= option to the ODS EXCEL statement.

There are several options you can add to the ODS EXCEL statement to control what the Excel file looks like. One of these options is OPTIONS, which itself has numerous suboptions. Suboptions are listed in parentheses after the keyword OPTIONS. For example, by default, titles are not included in the worksheet. The following statement uses the SHEET_NAME= suboption to specify a name for the Excel worksheet and the EMBEDDED_TITLES= suboption to embed titles in the worksheet:

ODS EXCEL OPTIONS(SHEET_NAME = 'Golf Data' EMBEDDED_TITLES = 'ON');

The AUTOFILTER= suboption allows you to set up filtering for specified columns in the worksheet. Filtering in Excel allows you to easily sort your data and exclude specific rows in the worksheet. For example, the following statement turns on filtering for columns 2 through 10 of the Golf Data worksheet:

ODS EXCEL OPTIONS(SHEET_NAME = 'Golf Data' AUTOFILTER = '2-10');

Other suboptions allow you to control things like column width, freezing of headers, starting cells for the output, and when to create a new sheet.

Example  This example uses the permanent SAS data set, GOLF (created in Section 10.2), which has information about golf courses in Hawaii. The following program uses ODS to create an Excel file, golfinfo.xlsx, which contains two worksheets. The first worksheet contains the results of a PROC PRINT, while the second contains the results of a PROC MEANS.

LIBNAME travel 'c:MySASLib';

*Create one Excel file with two worksheets;

OPTIONS MISSING = '';

ODS EXCEL FILE = 'c:MyExcelgolfinfo.xlsx';

ODS EXCEL OPTIONS(SHEET_NAME = 'GolfData' AUTOFILTER = '1-5');

PROC PRINT DATA = travel.golf NOOBS;

RUN;

ODS NOPROCTITLE;

ODS EXCEL OPTIONS(SHEET_NAME = 'GolfMeans' AUTOFILTER = 'NONE');

PROC MEANS DATA = travel.golf MAXDEC = 0 MEAN MIN MAX;

   CLASS Par;

   VAR Yardage GreenFees;

RUN;

ODS EXCEL CLOSE;

Notice that the program contains several ODS statements. The first ODS statement specifies the output file. The second ODS statement, which is in effect for the PROC PRINT, tells SAS to name the worksheet GolfData, and to set up filters for columns 1 through 5. The ODS NOPROCTITLE statement prevents the name of the next procedure (MEANS) from appearing in the first row of the worksheet. The fourth ODS statement, which is in effect for the PROC MEANS, tells SAS to name the worksheet GolfMeans and not to include the filters. The final ODS statement closes the Excel file.

Here is the first worksheet, GolfData, created by the PROC PRINT. Note the filters on columns A through E (1-5). These filters allow you to easily sort and filter your data in Excel.

 

Here is the second worksheet, GolfMeans, created by the PROC MEANS.

 

 

10.6   Writing Raw Data Files with the DATA Step

When you need total control over the contents and format of raw data files that you are creating, then the DATA step is the way to go. Using FILE and PUT statements in the DATA step, you can write almost any form of raw data file.

You can write raw data the same way that you read raw data in a DATA step, with just a few changes. Instead of naming the external file in an INFILE statement, you name it in a FILE statement. Instead of reading variables with an INPUT statement, you write them with a PUT statement. In other words, you use INFILE and INPUT statements to get raw data into SAS, and FILE and PUT statements to get raw data out.

PUT statements can be in list, column, or formatted style, just like INPUT statements, but since SAS already knows whether a variable is numeric or character, you don’t have to put a $ after character variables. If you use list style PUT statements, SAS will automatically put one space between each variable, creating a space-delimited file. To write files with other delimiters, use a list-style PUT statement and the DSD and DLM= options in your FILE statement. These options are covered in more detail in Section 2.18. Here is the general form of the FILE statement with the DSD and DLM= options:

FILE 'file-specification' DSD DLM = 'delimiter';

If you use column or formatted styles of PUT statements, SAS will put the variables wherever you specify. You can control spacing with the same pointer controls that INPUT statements use: @n to move to column n, +n to move n columns, / to skip to the next line, #n to skip to line n, and the trailing @ to hold the current line. In addition to printing variables, you can insert a text string by simply enclosing it in quotation marks.

Example  This example uses the same SAS data set created in Section 10.2 containing information about Hawaiian golf courses.

	CourseName	Holes	Par	Yardage	GreenFees
1	Pukalani	18	72	6945	89
2	Kahili	18	72	6570	119
3	Maui Nui Blue	18	71	6404	69
4	Waiehu Municipal	18	72	6330	63
5	Wailea Gold	18	72	.	250
6	Dunes at Maui Lani	18	72	6841	105
7	Kaanapali	18	71	6555	144

This example shows how much more control you have when using the DATA step as opposed to PROC EXPORT or ODS. Suppose you want to put the data in a raw data file, but with only three variables, in a new order, and with dollar signs added to the variable GreenFees. The following program reads the SAS data set and writes a raw data file using FILE and PUT statements:

LIBNAME travel 'c:MySASLib';

*Create raw data file using FILE and PUT statements;

DATA _NULL_;

   SET travel.golf;

   FILE 'c:MyRawDataNewfile.dat';

   PUT CourseName 'Golf Course' @32 GreenFees DOLLAR7.2 @40 'Par ' Par;

RUN;

The word _NULL_ appears in the DATA statement instead of a SAS data set name. You could put a data set name there, but _NULL_ is a special keyword that tells SAS not to bother making a new SAS data set. By not writing a new SAS data set, you save computer resources.

The SET statement simply tells SAS to read the permanent SAS data set GOLF. The FILE statement tells SAS the name of the output file you want to create. Then the PUT statement tells SAS what to write and where. The PUT statement contains two quoted strings, “Golf Course“ and “Par “ which SAS inserts in the raw data file. The PUT statement also tells SAS exactly where to place the data values for each variable using the @ column pointer, and to use the DOLLAR7.2 format to write the values for the GreenFees variable.

If you run this program, your log will contain the following note telling how many records were written to the output file:

Here is the text file, Newfile.dat, created in the DATA _NULL_ step:

Pukalani Golf Course            $89.00 Par 72

Kahili Golf Course             $119.00 Par 72

Maui Nui Blue Golf Course       $69.00 Par 71

Waiehu Municipal Golf Course    $63.00 Par 72

Wailea Gold Golf Course        $250.00 Par 72

Dunes at Maui Lani Golf Course $105.00 Par 72

Kaanapali Golf Course          $144.00 Par 71