Chapter 9 Exporting Data

 

Topics

 

9.1 Methods for Exporting Your Data

9.2 Writing Files Using the Export Wizard

9.3 Writing Delimited Files with the EXPORT Procedure

9.4 Writing PC Files with the EXPORT Procedure

9.5 Writing Raw Data Files with the DATA Step

9.6 Writing Delimited and HTML Files Using ODS

9.7 Sharing SAS Data Sets with Other Types of Computers

 

9.1 Methods for Exporting Your Data

 

SAS offers many options for transferring data from one application to another or from one type of computer to another.

 

Exporting data to other applications

 

The types of files that you can create and the methods available depends on what OS you are using and whether you have SAS/ACCESS software. There are three general methods for exporting data to other applications: create delimited or text files that the other software can read; create files in formats like HTML, RTF or XML that the other software can read; or write the data in the other software's native format.

Exporting SAS data sets to other operating environments

 

Back to Top

 

9.2 Writing Files Using the Export Wizard

 

The Export Wizard offers an easy way to produce files that can be imported into other software. It is actually a graphical user interface to the EXPORT procedure. Start the Export Wizard by select Export Data... from the File menu.

 

Back to Top

 

9.3 Writing Delimited Files with the EXPORT Procedure

 

The general form is

 

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

 

For example,

 

PROC EXPORT DATA=hotels OUTFILE='c:\MyData\Hotels.csv';

 

SAS uses 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. If you specify the DBMS option, it takes precedence over the file extension.

 

Type of File                                              Extension                        DBMS Identifier

Comma-delimited                                     .csv                                 CSV

Tab-delimited                                           .txt                                  TAB

Space-delimited                                                                              DLM

 

You must use the DBMS= option when dealing with space-delimited files since there is no standard extension for them. Also, you can tell SAS to replace any file with the same name by the REPLACE option. You can use the DELIMITER statement to specify a delimiter other than a comma, tab, or space. If a DELIMITER statement is present, it does not matter what file extension you use, or what DBMS identifier you specify, the file will have the delimiter that you specify in the DELIMITER statement.

 

Back to Top

 

9.4 Writing PC Files with the EXPORT Procedure

 

If you have SAS/ACCESS for PC File Formats software, then you can use the EXPORT procedure to create PC file types in addition to delimited files. With Windows, the EXPORT procedure can create Access, Excel, dBase and Lotus files. With UNIX, it can create dBase files, and starting with SAS 9.1, UNIX users can also create Access and Excel files.

 

Writing PC files with the EXPORT procedure is similar to reading PC files with the IMPORT procedures. See 2.15 and SAS Help and Documentation.

 

Back to Top

 

9.5 Writing Raw Data Files with the DATA Step

 

The DATA step gives you flexibility to create raw data files. You can write raw data the same way that you read raw data, with just a few changes. You name the external file in a FILE statement and write variables with a PUT statement. PUT statements can be in list, column or formatted style, just like INPUT statements, but since SAS knows whether a variable is numeric or character, you don't have to put a $ to indicate character variables. If you use list format, 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 the FILE statement. See Chapter 2 for a discussion of the DSD and DLM= options. 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, +n, /, #n and the trailing @ to hold the current line. You can also insert a text string by simply enclosing it in quotation marks.

 

Example program (Click here for the Golf data):

 

LIBNAME travel 'v:\';

DATA travel.golf;

    INFILE 'v:\Golf.txt';

    INPUT CourseName $18. NumberOfHoles Par Yardage GreenFees;

RUN;

 

Suppose you want to put the data in a raw data file, but with only 3 variables, in a new order, and with dollar signs added to the variable GreenFees.

 

LIBNAME activity 'v:\';

DATA _NULL_;

    SET activity.golf;

    FILE 'v:\GolfNew.txt';

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

RUN;

 

Back to Top

 

9.6 Writing Delimited and HTML Files Using ODS

 

The ODS can create all kinds of output formats. Among the various output formats that ODS can create are CSV and HTML, which are useful for transferring data from SAS to other applications. You can use this method in any operating environment. You use ODS to export data by choosing the appropriate output destination for your application and using PROC PRINT to get a listing of your data. By default, SAS will print a period for any missing numeric data. If you want SAS to print nothing for missing numeric data, then you can use the MISSING='' system option. You can also specify NOOBS on the PROC PRINT statement if you don't want observations numbers in your output file.

 

Starting with SAS 9, you can use ODS to create CSV files. CSV files have commas separating all the data values and the values are enclosed in double quotation marks. Commas are allowed as part of the values thanks to the double quotation marks.

 

ODS CSV FILE='filename.csv';

    PROC PRINT statements go here

RUN;

ODS CSV CLOSE;

 

In particular, if you need titles and footnotes in the CSV file, then use the CSVALL output destination instead of CSV.

 

Use the following statements to generate an HTML file of you data with the default style. You can choose a different style by adding the STYLE= option to the ODS HTML statement. Or, if you don't want any styling, then use CHTML (Compact HTML---available beginning with SAS 9) output destination instead of HTML.

 

ODS HTML FILE='filename.html';

    PROC PRINT statement go here

RUN;

ODS HTML CLOSE;

 

Example program (See the Golf data in 9.5):

 

LIBNAME travel 'v:\';

ODS CSV FILE='v:\Golf.csv';

PROC PRINT DATA=travel.golf;

    TITLE 'Golf Course Info';

RUN;

ODS CSV CLOSE;

 

LIBNAME travel 'v:\';

ODS HTML FILE='v:\Golf.html';

PROC PRINT DATA=travel.golf NOOBS;

    TITLE 'Golf Course Info';

RUN;

ODS HTML CLOSE;

 

Back to Top

 

9.7 Sharing SAS Data Sets with Other Types of Computers

 

At times you need to use your SAS data sets on a computer with a different operating environment than the one where they were created. Although SAS will automatically use Cross Environment Data Access (CEDA) to translate the data into a form that SAS in your operating environment can understand, you might want to create the data sets in the representation of the other computer. That way when the other computer accesses the data, it doesn't have to waste resources translating the data to its own format. You do this by using the OUTREP= option on either the LIBNAME statement, if you want all the data sets in that library to have the specified host representation, or as a data set option if you only want it to apply to one data set. The general form is

 

LIBNAME libref 'path' OUTREP=data-representation;

 

or

 

data-set-name (OUTREP=data-representation)

 

The data-representation is basically the name of the operating environment. For example, WINDOWS_64 for Windows 64-Bit Edition and SOLARIS_32 for Solaris 32-Bit Edition. See SAS Help for a complete list of possible values.

 

Example:

 

LIBNAME sports 'v:\';

DATA sports.GolfLinux (OUTREP=LINUX);

    SET sprots.golf;

RUN;

 

SAS data sets created beginning with SAS Version 7 by default have the extension .sas7bdat. Some Windows systems that use FAT (File Allocation Table) file systems can only have files with 3-character extensions, so for these systems the extension for SAS data sets is .sd7. If you receive a SAS data set with a 3 letter extension, or if you need to create a SAS data set with a 3 letter extension, then use the SHORTFILEEXT option in the LIBNAME statement for the data set.

 

LIBNAME libref 'path' SHORTFILEEXT;

 

Back to Top

Back to the Listing of Lecture Notes