Creating Raw Data Files
Using SAS
®
Creating Raw Data Files Using SAS® Transcript was developed by Mike Kalt. Additional contributions were made by Michele Ensor, Mark Jordan, Kathy Passarella, and Cynthia Zender. Editing and
production support was provided by the Curriculum Development and Support Department.
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. Other brand and product names are trademarks of their respective companies.
Creating Raw Data Files Using SAS® Transcript
Copyright © 2009 SAS Institute Inc. Cary, NC, USA. All rights reserved. Printed in the United States of America. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, or otherwise, without the prior written permission of the publisher, SAS Institute Inc.
Book code E1388, course code RLSPFPUT, prepared date 20Mar2009. RLSPFPUT_001
For Your Information iii
Table of Contents
Lecture Description ... iv
Prerequisites ... v
Creating Raw Data Files Using SAS® ... 1
1. Overview ... 5
2. Creating Raw Data Files Using the EXPORT Procedure ... 22
3. Creating Raw Data Files Using the Output Delivery System ... 42
4. Creating Raw Data Files Using the DATA Step ... 53
Lecture Description
This lecture shows how to create raw data files from SAS data sets. You learn how to recognize the structure of different types of raw data files, use the EXPORT procedure to create raw files, use the ODS CSVALL statement to create raw files from SAS procedure output, and use FILE and PUT statements in the DATA step to create raw files.
To learn more…
For information on other courses in the curriculum, contact the SAS Education Division at 1-800-333-7660, or send e-mail to [email protected]. You can also find this information on the Web at support.sas.com/training/ as well as in the Training Course Catalog.
For a list of other SAS books that relate to the topics covered in this
Course Notes, USA customers can contact our SAS Publishing Department at 1-800-727-3228 or send e-mail to [email protected]. Customers outside the USA, please contact your local SAS office.
Also, see the Publications Catalog on the Web at support.sas.com/pubs for a complete list of books and a convenient order form.
For Your Information v
Prerequisites
Before listening to this lecture, you should be familiar with the DATA step, the PRINT procedure, and FILE and PUT statement syntax. You should also understand IF-THEN/ELSE logic, and be able to view raw data using text editors. You can gain this knowledge by completing the SAS® Programming 1: Essentials course.
Creating Raw Data Files Using SAS
®
1. Overview ... 5
2. Creating Raw Data Files Using the EXPORT Procedure ... 22
3. Creating Raw Data Files Using the Output Delivery System ... 42
4. Creating Raw Data Files Using the DATA Step ... 53
1. Overview 3
Creating Raw Data Files
Using SAS
®Welcome to the SAS e-Lecture Creating Raw Data Files Using SAS®. My name is Mike and I am an instructor for SAS. Today we will be discussing how to use SAS to create raw data files that you can transfer to other software applications.
1. Overview
2. Creating Raw Data Files Using the EXPORT Procedure
3. Creating Raw Data Files Using the Output Delivery System
4. Creating Raw Data Files Using the DATA Step
5. Summary
Creating Raw Data Files Using SAS
2
Our lecture will be presented in five sections.
First, I'll provide a general overview of raw data files, including reasons for creating raw files and different types of files that you can create. The next three sections will cover how to create raw files by using different programming methods in SAS.
In Section 2, I'll cover how to use the EXPORT procedure to create raw data files with delimiter characters.
In Section 3, I'll show how to use the Output Delivery System to create raw files with delimiters. In Section 4, I'll discuss how to create raw files using FILE and PUT statements in the DATA step. Finally, in Section 5, I'll review the pros and cons of each method and help you determine which method is best for your needs.
1. Overview 5
1. Overview
1. Overview
2. Creating Raw Data Files Using the EXPORT Procedure
3. Creating Raw Data Files Using the Output Delivery System
4. Creating Raw Data Files Using the DATA Step
5. Summary
Creating Raw Data Files Using SAS
3
Objectives
Distinguish between raw data files and other types of files.
Understand reasons for creating raw data files.
List types and attributes of raw data files.
Identify the methods for creating raw data files.
4
In this section I'll provide some basic information about raw data files. Some of the topics we'll cover are
• how to distinguish between raw data files and other types of files
• reasons for creating raw data files
• types of raw files and their attributes, and
1. Overview 7
What Is a Raw Data File?
Data is represented as readable characters.
Often raw data files have file extensions of .txt or .csv.
5
Smith,1500.00,919-555-3467
Wilson,2700.65,248-555-8653
Williamson,4165.80,303-555-8721
Lee,3267.00,215-555-3189
07JUL08 AX24 LAX DFW 1600 2010
07JUL08 RB104 DTW ORD 1350 1400
08JUL08 AX876 ATL BWI 900 1100
08JUL08 CZ12 CLE BOS 1005 1140
So, exactly what do we mean when we use the term raw data file? The most common form is a file where the data is represented as readable text characters.
On Windows and UNIX systems, raw files typically have a file extension of .txt or .csv.
Portions of two raw data files are shown here. Notice that in both cases the data values are clearly readable.
Examples of Data Files That Are Not Raw
SAS data sets
Microsoft Excel workbooks
Database tables – Oracle
– Microsoft Access – MySQL
6
You can distinguish between raw data files and files that are formatted for specific databases and software packages. Some examples of file types that are not raw data files include SAS data sets, Microsoft Excel workbooks, and tables for database products such as Oracle and Microsoft Access. These types of files are stored in special binary formats and cannot be viewed in a text editor.
1. Overview 9
Why Create Raw Data Files?
7
Now that we know what raw data files are, let's consider a situation where you might need to create one with SAS.
Suppose you have data stored in a SAS data set that you need to share with someone at another
organization, but that person doesn't have SAS. The person wants to process the data with software that he does have, such as Microsoft Excel.
SAS/ACCESS Software
8
SAS/ACCESS
SAS Data Set Excel Worksheet
One way to convert SAS data sets directly to other formats, such as Excel and Oracle, is to use SAS/ACCESS software. This is the preferred method to use when possible, and if you can use SAS/ACCESS, you don't have to create a raw data file.
1. Overview 11
SAS Data Set
Ö
Raw Data File
Ö
Target Application
9
SAS Data Set
Raw Data File
Excel Spreadsheet
If you can't use SAS/ACCESS to export your SAS data to a specific software format, you can create raw data files, which can be read by most software applications. This makes raw data files a universal format that you can use to send files to others who do not have SAS.
Decisions in Creating a Raw Data File
Format of the file – Delimited fields – Fixed column
10
If you know that you want to create a raw data file, you need to make some decisions about the type of raw file that you want, and what information you want the file to contain.
First, you need to decide on the format of the file. You can create raw files with data fields separated by delimiters, or with data in fixed columns.
1. Overview 13
Decisions in Creating a Raw Data File
Format of the file – Delimited fields – Fixed column
Additional text in file – Column headings – Titles/footnotes
11
Next, you need to decide whether you want additional text in the file, such as column headers, titles, or footnotes.
Delimited Fields versus Fixed Columns
12Smith,1500.00,919-555-3467
Wilson,2700.65,248-555-8653
Williamson,4165.80,303-555-8721
Lee,3267.00,215-555-3189
07JUL08 AX24 LAX DFW 1600 2010
07JUL08 RB104 DTW ORD 1350 1400
08JUL08 AX876 ATL BWI 900 1100
08JUL08 CZ12 CLE BOS 1005 1140
Fixed Columns
1234567891123456789212345678931
1234567890123456789012345678901
Delimited Fields
1. Overview 15
Delimited Fields versus Fixed Columns
13
Smith,1500.00,919-555-3467
Wilson,2700.65,248-555-8653
Williamson,4165.80,303-555-8721
Lee,3267.00,215-555-3189
Fields separated by
delimiter character
07JUL08 AX24 LAX DFW 1600 2010
07JUL08 RB104 DTW ORD 1350 1400
08JUL08 AX876 ATL BWI 900 1100
08JUL08 CZ12 CLE BOS 1005 1140
Fixed Columns
1234567891123456789212345678931
1234567890123456789012345678901
Delimited Fields
In a delimited file, data fields are separated by characters called delimiters. The most commonly used delimiter characters are commas, tab characters, and blanks.
An example of a comma-delimited file is shown here. Each record in the file contains three fields, separated by commas.
07JUL08 AX24 LAX DFW 1600 2010
07JUL08 RB104 DTW ORD 1350 1400
08JUL08 AX876 ATL BWI 900 1100
08JUL08 CZ12 CLE BOS 1005 1140
Delimited Fields versus Fixed Columns
14
Smith,1500.00,919-555-3467
Wilson,2700.65,248-555-8653
Williamson,4165.80,303-555-8721
Lee,3267.00,215-555-3189
Fixed Columns
1234567891123456789212345678931
1234567890123456789012345678901
Delimited Fields
Note that the length of the first field is different on each record, and the beginning locations of the second and third fields also vary.
1. Overview 17
Delimited Fields versus Fixed Column
15
Smith,1500.00,919-555-3467
Wilson,2700.65,248-555-8653
Williamson,4165.80,303-555-8721
Lee,3267.00,215-555-3189
Delimited Fields
Fixed Columns
Fields in same columns
on each record
1234567891123456789212345678931
1234567890123456789012345678901
07JUL08 AX24 LAX DFW 1600 2010
07JUL08 RB104 DTW ORD 1350 1400
08JUL08 AX876 ATL BWI 900 1100
08JUL08 CZ12 CLE BOS 1005 1140
In a fixed-column file, fields are in the same columns on each record. In the example here, the first field is always in columns 1 through 7 of each record, and the second field is always in columns 9 through 13, and so on.
Column Headers
16Name,Amount,Phone
Smith,1500.00,919-555-3467
Wilson,2700.65,248-555-8653
Williamson,4165.80,303-555-8721
Now let's consider other information you may want to add to the raw data file.
If you are creating a delimited file, you may want to include a line of column headers at the top. This is useful when you want to import the raw file into an application that can use the first row of data as column names.
1. Overview 19
Titles and Footnotes
17
Employee Salary and Contact Info
Name,Amount,Phone
Smith,1500.00,919-555-3467
Wilson,2700.65,248-555-8653
Williamson,4165.80,303-555-8721
AS OF 18JUL
If you are creating a delimited file that will be included as a report in a spreadsheet, you may want to add titles and footnotes to the file.
As we'll see later in the course, the information that you want to include in the file will dictate the method you use to create the file.
Methods for Creating Raw Data Files
Q The EXPORT Procedure (PROC EXPORT) Q Output Delivery System (ODS)
Q DATA Step with FILE and PUT Statements
18
In the next three sections of this lecture, we'll look at three different ways to create raw data files in SAS. They are
• using the EXPORT procedure
• using the Output Delivery System
• using a DATA step with FILE and PUT statements.
The best method to use depends on whether you want to create a delimited or fixed-column file, and whether you want to include column headers, titles, and footnotes in your file.
1. Overview 21
Summary
Raw data files
contain data in readable text form
can be used to export SAS data to other applications
can be delimited or fixed column
might have column headers, titles, and footnotes
can be created in several ways using SAS.
19
Let's review what we've learned in this section.
• First, raw data files are defined as files that contain data in readable form.
• Raw data files are often used as an intermediate file format when exporting SAS data to other applications.
• Data in raw data files can be in either delimited or fixed-column format.
• Optionally, raw data files can contain column headers, titles, and footnotes.
• Finally, there are several ways to create raw data files, which we'll look at in the next few sections of this lecture.
2. Creating Raw Data Files Using the EXPORT Procedure
1. Overview
2. Creating Raw Data Files Using
the EXPORT Procedure
3. Creating Raw Data Files Using the Output Delivery System
4. Creating Raw Data Files Using the DATA Step
5. Summary
Creating Raw Data Files Using SAS
20
In Section 2, I'll cover how to use the EXPORT procedure to create raw data files with delimiter characters.
2. Creating Raw Data Files Using the EXPORT Procedure 23
Objectives
Become familiar with basic EXPORT procedure syntax.
Use PROC EXPORT to create delimited files with commas and other delimiters.
Use the FSLIST procedure or text editor to view raw data files.
21
First I'll discuss the basic syntax for PROC EXPORT and show you how to use the procedure to create delimited files with commas or other delimiters.
We'll also cover how to use PROC FSLIST or a text editor to view the raw data files that you create with PROC EXPORT.
What Is PROC EXPORT?
PROC EXPORT can convert SAS data sets to external data sources:
database files and spreadsheets
raw data files
Converting SAS data sets directly to database files and spreadsheets requires SAS/ACCESS Interface for PC File Formats.
22
As I mentioned earlier, you can use PROC EXPORT to convert SAS data sets directly to files in commonly used formats, such as Microsoft Excel workbooks and Microsoft Access tables.
You can also use PROC EXPORT to create raw data files, which you can then import into database and spreadsheet applications.
In order to use PROC EXPORT to convert SAS data sets directly to database and spreadsheet files, you must have SAS/ACCESS Interface for PC File Formats licensed.
If you do have SAS/ACCESS, then using PROC EXPORT to create database files directly is generally the best approach.
2. Creating Raw Data Files Using the EXPORT Procedure 25
If You Do Not Have SAS/ACCESS Software
Use PROC EXPORT to create a raw data file.
Import the raw data file into database or spreadsheet application.
23
If you don't have SAS/ACCESS software, then you can still use PROC EXPORT to create a raw data file and then import the raw data file into your database or spreadsheet application.
The remainder of this section will cover how to use PROC EXPORT to create raw data files. For
information on using PROC EXPORT with SAS/ACCESS to create database and spreadsheet files, refer to the SAS Help facility.
Creating Raw Data Files with PROC EXPORT
When creating raw data files, PROC EXPORT
does not require SAS/ACCESS software
can create delimited files, but not fixed column files
enables you to specify the delimiter
inserts variable names as column headers
does not include title or footnote text.
24
You should be aware of the following when using PROC EXPORT to create raw data files:
• It does not require that you have SAS/ACCESS software licensed.
• You can create delimited files, but not fixed-column files.
• You can specify the delimiter that you want inserted between fields.
• Variable names are automatically inserted as column headers in the file. Title or footnote text is not included in the file.
2. Creating Raw Data Files Using the EXPORT Procedure 27
PROC EXPORT Syntax
25
PROC EXPORT DATA=<libref.>SAS-data-set
OUTFILE="filename" DBMS=CSV | TAB | DLM; General form of the EXPORT procedure:
Here is the basic syntax for PROC EXPORT. All of the information and options shown here are specified in the PROC EXPORT statement.
PROC EXPORT Syntax
26
PROC EXPORT DATA=<libref.>SAS-data-set OUTFILE="filename"
DBMS=CSV | TAB | DLM;
2. Creating Raw Data Files Using the EXPORT Procedure 29
PROC EXPORT Syntax
27
PROC EXPORT DATA=<libref.>SAS-data-set OUTFILE="filename" DBMS=CSV | TAB | DLM;
The OUTFILE= option specifies the name of the raw data file that you want to create. If you don't specify a full path, the file is created in the current directory.
PROC EXPORT Syntax
28
PROC EXPORT DATA=<libref.>SAS-data-set OUTFILE="filename" DBMS=CSV | TAB | DLM;
The DBMS option specifies the type of output that you want to create.
• A value of CSV produces a comma-delimited file.
• TAB produces a tab-delimited file.
DLM lets you specify your own delimiter character. If you specify a value of DLM, a blank is used as the default delimiter character.
2. Creating Raw Data Files Using the EXPORT Procedure 31
PROC EXPORT Syntax
29
PROC EXPORT DATA=<libref.>SAS-data-set OUTFILE="filename" DBMS=CSV | TAB | DLM; DELIMITER="delimiter-character";
If you want to use a character other than a blank, comma, or tab as your delimiter, specify DBMS=DLM. Then, specify the delimiter character that you want to use with the DELIMITER statement. Notice that this is a separate statement, and not an option in the PROC EXPORT statement.
PROC EXPORT Syntax
30
PROC EXPORT DATA=<libref.>SAS-data-set OUTFILE="filename" DBMS=CSV | TAB | DLM; PUTNAMES=YES|NO;
You can use the PUTNAMES statement to specify whether you want the output file to contain a header row with variable names. The default is PUTNAMES=YES, which writes out column headers. Specify PUTNAMES=NO if you don't want a header row.
2. Creating Raw Data Files Using the EXPORT Procedure 33
PROC EXPORT Syntax
31
PROC EXPORT DATA=<libref.>SAS-data-set OUTFILE="filename" DBMS=CSV | TAB | DLM LABEL;
If you want the header row to contain variable labels instead of variable names, you can specify the LABEL option in the PROC EXPORT statement.
PROC EXPORT Syntax
32
PROC EXPORT DATA=<libref.>SAS-data-set OUTFILE="filename" DBMS=CSV | TAB | DLM REPLACE;
If the raw file that you want to create already exists, then by default, PROC EXPORT will not replace it. To replace an existing file, specify the REPLACE option.
2. Creating Raw Data Files Using the EXPORT Procedure 35
Creating a Comma-Delimited File
33
proc export data=orion.sales outfile="sales.csv"
dbms=csv replace; run;
Orion.Sales
Order_ID Order_type Customer_ID Order_Date 1241054779 3 24 02JAN2007 1241063739 3 89 03JAN2007 1241066216 3 171 04JAN2007 1241086052 3 53 06JAN2007 1241147641 1 53 13JAN2007
Here's an example of creating a comma-delimited file. In this case, we want to take an existing SAS data set, orion.sales, and create a comma-separated file called sales.csv in the current working directory. Note that in orion.sales, the variable Order_Date is defined with a DATE9. format. In our PROC EXPORT statement, we specify the name of the SAS data set that we want to use to create the raw file, the name of the file we want to create, and the type of file we want to create.
Viewing the Raw Data File
34
PROC FSLIST FILEREF="filename";
Once you've created your raw data file, you can view it in your SAS session using the FSLIST procedure. In the PROC FSLIST statement, use the FILEREF= option to specify the name of the file that you want to display.
2. Creating Raw Data Files Using the EXPORT Procedure 37
Viewing the Raw Data File
35
PROC FSLIST FILEREF="filename";
proc fslist fileref="sales.csv"; run;
Viewing the Raw Data File
36
PROC FSLIST FILEREF="filename";
proc fslist fileref="sales.csv"; run;
And here's what it looks like in the FSLIST window. Note that PROC EXPORT created the raw data file using the formatted value of Order_Date.
If you are using SAS Enterprise Guide or SAS Learning Edition, PROC FSLIST is not available. In those environments, you can view the raw file in any text editor, such as Windows Notepad.
2. Creating Raw Data Files Using the EXPORT Procedure 39
Using PROC EXPORT to Create Delimited Files
This demonstration illustrates how to use PROC EXPORT to create delimited files and view the files using PROC FSLIST or a text editor.
37
Now, let's look at a demonstration of some of the other ways to create raw data files using PROC EXPORT.
In this demo, we'll see how to create various types of delimited files with PROC EXPORT and view the files with PROC FSLIST. We'll start by creating a comma-delimited file.
1. First, let's submit a PROC EXPORT step that takes the SAS data set orion.sales and creates a comma-delimited file called sales.csv. When creating a comma-delimited file under Windows or UNIX, the output file normally has a file extension of .csv, and you should specify DBMS=CSV in the PROC EXPORT statement. Since we have not specified a full pathname for our output file, it will be stored in our default home directory which is, in this instance, c:\electure.
2. Looking at the SAS log, notice that PROC EXPORT actually generates a DATA step to produce the file. The log tells us that the file sales.csv has five records.
3. To look at the file we created, we can submit a PROC FSLIST step.
4. PROC FSLIST opens a window that displays the comma-delimited file that we created. 5. Before we can go to the next step, we need to close the FSLIST window.
6. Now let's create a tab-delimited file. To do this, we need to make some changes to our PROC EXPORT code.
1) First, we need to change the value of the DBMS= option to TAB.
2) We should change the extension of our output file to .txt since it is no longer a comma-delimited file.
7. When we submit the code, the SAS log tells us that the file sales.txt has been created with five records.
9. Note that the records now display empty boxes between the fields. These are actually tab characters, which are unprintable. PROC FSLIST displays unprintable characters as boxes.
10. Now let's create a delimited file that uses a pound sign as our delimiter.
1) First, we need to change the value of the DBMS= option to DLM to indicate that we want to create a delimited file and that we will specify the delimiter.
2) Next, we add a DELIMITER statement to specify that we want a pound sign as our delimiter. 11. When we submit the code, the SAS log tells us that the file sales.txt has been created with five
records.
12. We could use PROC FSLIST to view the raw file, but we can also use any text editor, such as Windows Notepad. If you use SAS Learning Edition or SAS Enterprise Guide, PROC FSLIST is not available to you, so in these environments you need to use a text editor. So, let's open the file with Windows Notepad.
2. Creating Raw Data Files Using the EXPORT Procedure 41
Summary
With SAS/ACCESS, PROC EXPORT can be used to convert SAS data sets to database or spreadsheet format.
Use PROC EXPORT to create raw data files if you do not have SAS/ACCESS.
PROC EXPORT creates delimited files.
Options in PROC EXPORT control the choice of delimiter and whether column headings are produced.
Use the FSLIST procedure or text editor to view raw data files.
38
Let's review what we've covered in this section.
• First, if you have SAS/ACCESS software, you can use PROC EXPORT to convert SAS data sets directly to a database file or spreadsheet.
• If you don't have SAS/ACCESS, you can use PROC EXPORT to create raw data files that can be read by other applications.
• PROC EXPORT creates delimited raw files, and you can use options to control the delimiter that is used and whether variable names are used as column headings.
• Finally, if you want to view the raw data file that you've created, you can use the FSLIST procedure or any text editor.
In the next section, we'll look at another method for creating raw data files, the Output Delivery System, or ODS.
3. Creating Raw Data Files Using the Output Delivery
System
1. Overview
2. Creating Raw Data Files Using the EXPORT Procedure
3. Creating Raw Data Files Using the
Output Delivery System
4. Creating Raw Data Files Using the DATA Step
5. Summary
Creating Raw Data Files Using SAS
39
3. Creating Raw Data Files Using the Output Delivery System 43
Objectives
Use the ODS CSVALL statement to write delimited files containing data and column headers.
Add footnotes and titles to delimited files.
40
I'll begin by describing how to use the ODS CSVALL statement to write delimited files containing data and column headers. Then I'll show you how to use ODS to add footnotes and titles to delimited files.
What Is the Output Delivery System (ODS)?
ODS enables you to send SAS report output to a variety of locations and to specify the format of the output for each destination.
41
O
D
S
Listing
HTML
SAS
Output
CSV
The Output Delivery System lets you send SAS reports to a variety of locations and specify the output format for each destination.
By default, output from SAS procedures goes to the Listing destination, which is the Output Window if you are running SAS interactively.
You can also send output to an HTML file, a PDF file, to a comma-delimited file, or to any of several other destinations.
3. Creating Raw Data Files Using the Output Delivery System 45
When to Use ODS?
ODS can be used with reporting procedures such as PROC PRINT or PROC REPORT to
create delimited files
include variable names as column headers
include titles and footnotes in the file.
42
ODS is used when SAS procedures produce output. For creating raw data files, ODS is helpful when you want to use a reporting procedure such as PROC PRINT or PROC REPORT, and you want to
• create delimited files with commas or other delimiters
• include variable names as column headers
ODS CSVALL Statement
The ODS CSVALL statement creates a delimited file using the output from a SAS procedure.
General form of the ODS CSVALL statement:
By default, fields are delimited with commas.
43
ODS CSVALL FILE='file-name'; <code to produce report> ODS CSVALL CLOSE;
To create a comma-delimited file with ODS, use the ODS CSVALL statement.
Submit the ODS CSVALL statement before you run the procedure that produces your report. In the ODS CSVALL statement, use the FILE= option to specify the name of the delimited file that you want to create. After the procedure statements, submit an ODS CSVALL CLOSE statement to close the raw file.
3. Creating Raw Data Files Using the Output Delivery System 47
ODS CSVALL Statement
44 "Order_ID","Order_Type","Customer_ID","Order_Date" 1241054779,3,24,"02JAN2007" 1241063739,1,89,"03JAN2007" 1241066216,1,171,"04JAN2007" 1241086052,3,53,"06JAN2007" 1241147641,1,53,"13JAN2007"
In this example, we want to create a file called sales.csv that contains the output from PROC PRINT, with column headers and data fields separated by commas.
ODS CSVALL Statement
45
ods csvall file='sales.csv';
proc print data=orion.sales noobs; run;
ods csvall close;
"Order_ID","Order_Type","Customer_ID","Order_Date" 1241054779,3,24,"02JAN2007" 1241063739,1,89,"03JAN2007" 1241066216,1,171,"04JAN2007" 1241086052,3,53,"06JAN2007" 1241147641,1,53,"13JAN2007"
To do this, we submit an ODS CSVALL statement, specifying the name of the output file. Next, we submit a PROC PRINT step to produce the output. Finally, we submit an ODS CSVALL CLOSE statement to close the output file. Note that the ODS CSVALL CLOSE statement must come after the RUN statement for PROC PRINT.
In the raw file that is produced using ODS CSVALL, note that the column header fields and formatted numeric values are enclosed in double quotation marks.
This is a common standard, and most applications, such as Microsoft Excel, handle the quote automatically.
3. Creating Raw Data Files Using the Output Delivery System 49
ODS CSVALL Statement with Titles and Footnotes
46
Low Volume Sales
"Order_ID","Order_Type","Customer_ID","Order_Date" 1241054779,3,24,"02JAN2007" 1241063739,1,89,"03JAN2007" 1241066216,1,171,"04JAN2007" 1241086052,3,53,"06JAN2007" 1241147641,1,53,"13JAN2007" First 2 Weeks of January
title "Low Volume Sales";
footnote "First 2 Weeks of January"; ods csvall file='csvsales.csv'; proc print data=orion.sales noobs; run;
ods csvall close;
If you add titles or footnotes to your PROC PRINT output, they are included in the file produced by ODS CSVALL. In this example, we have added TITLE and FOOTNOTE statements to the program we ran in the previous example. The output file contains the title and footnote.
ODS CSVALL Statement with Alternative Delimiters
The ODS CSVALL statement creates a delimited file from a SAS procedure.
General form of the ODS CSVALL statement with the DELIMITER= suboption:
47
ODS CSVALL FILE='file-name' OPTIONS(DELIMITER='delimiter'); <code to produce report>
ODS CSVALL CLOSE;
ods csvall file='sales_colon.txt' options(delimiter=':');
ods csvall file='sales_tab.txt' options(delimiter='09'x);
To create a delimited file with ODS with a delimiter other than a comma, use the DELIMITER= suboption in the ODS CSVALL statement. The syntax for the DELIMITER= suboption is shown here. In our first example, we're specifying that a colon be used as the delimiter between fields.
In the second example, we request a tab character as a delimiter by specifying the ASCII hexadecimal code for the tab character.
3. Creating Raw Data Files Using the Output Delivery System 51
Notes on Using ODS CSVALL
ODS CSVALL produces comma-delimited files by default. You can specify other delimiters by using the DELIMITER= suboption.
ODS CSVALL does not produce fixed-column files.
Files created with ODS CSVALL and PROC PRINT contain variable names or labels as column headers.
If formats are used with the procedure producing the output, formatted values are output to the raw data file.
Values for formatted numeric variables and character variables are enclosed in quotation marks.
48
Here are some things to be aware of if you plan to use ODS CSVALL to produce a raw data file:
• ODS CSVALL produces comma-delimited files by default, and you can specify other delimiters by using the DELIMITER= suboption. ODS CSVALL does not produce files in fixed-column format.
• Files created with ODS CSVALL and PROC PRINT always contain variable names or labels as column headers.
• If you assign formats to the variables used in the procedure, formatted values are output to the raw data file.
Summary
The ODS CSVALL statement creates delimited files from reports produced by PROC PRINT and other procedures.
Use the ODS CSVALL statement before the PROC step.
Use ODS CSVALL CLOSE after the PROC step.
Titles and footnotes are included in the raw data files.
49
Let's briefly review what we've covered in this chapter:
• You can use the ODS CSVALL statement when you want to produce comma-delimited files from a SAS procedure such as PROC PRINT.
• When you use this method, include an ODS CSVALL statement before the procedure and an ODS CSVALL CLOSE statement after the procedure runs.
• When you use ODS CSVALL, any titles or footnotes that are in effect are included in the raw data file that is created.
In the next chapter, we'll see how to create a raw data file using FILE and PUT statements in a DATA step.
4. Creating Raw Data Files Using the DATA Step 53
4. Creating Raw Data Files Using the DATA Step
1. Overview
2. Creating Raw Data Files Using the EXPORT Procedure
3. Creating Raw Data Files Using the Output Delivery System
4. Creating Raw Data Files Using the DATA Step
5. Summary
Creating Raw Data Files Using SAS
50
Objectives
Describe basic syntax for using FILE and PUT statements in the DATA step.
Use FILE and PUT statements to create delimited files.
Add column headers, titles, and footnotes to files created with FILE and PUT statements.
Use FILE and PUT statements to create fixed-column files.
51
Here are the topics we'll cover in this section:
• the basic syntax for using FILE and PUT statements in the DATA step
• how to use FILE and PUT statements to create delimited files
• how to add column headers, titles, and footnote to files created with FILE and PUT statements
4. Creating Raw Data Files Using the DATA Step 55
The DATA Step
You can use the DATA step to write
data to a raw file
a custom report.
52
You can use the DATA step to write data to a raw file or to produce highly customized reports. We'll focus on creating raw files in this lecture. Using the DATA step for report writing is covered in a separate lecture.
The DATA Step
53 READING FROM AN EXTERNAL FILE WRITING TO AN EXTERNAL FILEYou can think of the process for writing a raw data file from a SAS data set as being the reverse of the process for creating a SAS data set by reading in a raw file.
4. Creating Raw Data Files Using the DATA Step 57
The DATA Step
The DATAstatement begins the DATA step.
The DATAstatement begins the DATA step. 54 READING FROM AN EXTERNAL FILE WRITING TO AN EXTERNAL FILE
The DATA Step
The DATAstatement begins the DATA step.
The INFILE statement identifies an external file to read with an INPUT statement.
The DATAstatement begins the DATA step. 55 READING FROM AN EXTERNAL FILE WRITING TO AN EXTERNAL FILE
When reading in a raw data file to create a SAS data set, you use an INFILE statement to identify the raw file. The INFILE statement identifies an external file to read with an INPUT statement.
4. Creating Raw Data Files Using the DATA Step 59
The DATA Step
The DATAstatement begins the DATA step.
The INFILE statement identifies an external file to read with an INPUT statement.
The DATAstatement begins the DATA step.
The FILEstatement identifies an external file to write with a PUT statement. 56 READING FROM AN EXTERNAL FILE WRITING TO AN EXTERNAL FILE
To write a raw data file, you use a FILE statement to identify the file. The FILE statement identifies an external file to write with a PUT statement.
The DATA Step
The DATAstatement begins the DATA step.
The INFILE statement identifies an external file to read with an INPUT statement.
The INPUTstatement describes the arrangement of values in the input data record.
The DATAstatement begins the DATA step.
The FILEstatement identifies an external file to write with a PUT statement. 57 READING FROM AN EXTERNAL FILE WRITING TO AN EXTERNAL FILE
When reading in a raw data file to create a SAS data set, you use an INPUT statement to describe the arrangement of values in the input data record.
4. Creating Raw Data Files Using the DATA Step 61
The DATA Step
The DATAstatement begins the DATA step.
The INFILE statement identifies an external file to read with an INPUT statement.
The INPUTstatement describes the arrangement of values in the input data record.
The DATAstatement begins the DATA step.
The FILEstatement identifies an external file to write with a PUT statement.
The PUTstatement describes the arrangement of values in the output data record. 58 READING FROM AN EXTERNAL FILE WRITING TO AN EXTERNAL FILE
When writing a raw file, you use a PUT statement to describe the arrangement of values in the output data record.
The DATA Statement
To initiate a DATA step but not create a new data set, use a DATA statement.
General form of the DATA statement:
59
DATA _NULL_;
Let's look more closely at the statements used to create a raw data file, starting with the DATA statement. The DATA statement initiates the DATA step and is normally used to identify the name of the SAS data set that you want to create in the step. But if all you want to do is use a DATA step to create a raw file from an existing data set, you don't need to create a new SAS data set. If this is the case, you can use a DATA _NULL_ statement to tell SAS to start a DATA step but not create a new SAS data set.
4. Creating Raw Data Files Using the DATA Step 63
The FILE Statement
The FILE statement specifies the output destination for subsequent PUT statements.
General form of the FILE statement:
60
FILE file-specification<options>;
The FILE Statement
The FILE statement specifies the output destination for subsequent PUT statements:
For example:
61
file 'sales.txt';
FILE file-specification<options>;
file 'c:\myfiles\sales.txt'; or
4. Creating Raw Data Files Using the DATA Step 65
The FILE Statement
To send output to the SAS Output window for preview, use the following:
62
file print;
You may want to preview what the output file will look like by writing the lines to the Output window instead of the file itself. To do this, you can use a FILE PRINT statement. Notice that the word PRINT is not in quotation marks.
After you preview the output, you can later modify the FILE statement to have it point to the file you want to create.
The PUT Statement
63
The PUT statement writes lines to the destination specified in the FILE statement.
With simple list output, list the names of the variables whose values you want written.
General form of the PUT statement:
PUT variable-1 variable-2 … variable-n;
The PUT statement writes lines to the destination specified in the FILE statement.
The most basic form of the PUT statement is called simple list output. To create simple list output, list the names of the variables whose values you want on the output line. The PUT statement writes a variable value, inserts a single blank, and then writes the next value.
4. Creating Raw Data Files Using the DATA Step 67
Using FILE and PUT Statements
to Create a Delimited File
This demonstration illustrates how to use FILE and PUT statements in a DATA step to create a delimited file.
64
Let's look at an example of using FILE and PUT statements to create a raw file with fields delimited with blanks.
In this demo, we'll be using a data set called orion.sales2, which is identical to the data set orion.sales that we used in the last example, except that the variable order_date does not have a format associated with it.
In this demo we're using a FILE PRINT statement so that the output is written to the Output window. The PUT statement lists the variables we want output, separated by blanks.
When we go to the Output window, notice that each field is separated by a blank, which is the default delimiter. Notice also that the unformatted value for order_date is output.
Modified List Output
Modified list output enables you to specify a SAS format to control how the variable values are written.
To use modified list output, use a colon in the PUT statement between the variable name and the format.
65
PUT variable-1 : format-1.
variable-2 : format-2. …
variable-n : format-n.;
In our demo, we saw that using simple list output does not display formatted variable values unless the variables already have formats associated with them.
To write out formatted values, we can use a variation of simple list output called modified list output. To use modified list output, enter a colon after the variable name, followed by the format you want to use for the value.
4. Creating Raw Data Files Using the DATA Step 69
Using Modified List Output
to Create a Delimited File
This demonstration illustrates how to use PUT statements with modified list output to create a delimited file containing formatted values.
66
Let's take the program we ran in the previous demo and change the PUT statement to use modified list output.
To do this, we simply add a colon and a format name after the variable name order_date in the PUT statement.
Specifying an Alternate Delimiter
Use the DLM= option in the FILE statement to create a file with an alternate delimiter (other than a blank).
You can also specify the delimiter as a hexadecimal value.
67
FILE file-specification DLM='quoted-string' <other-options>;
file 'sales.txt' dlm='09'x; /* ASCII TAB character */
By default, both simple and modified list output use blanks to separate the values in the output. You can specify a different delimiter character by using the DLM= option in the FILE statement.
Here we show an example of specifying a tab character as the delimiter between fields. Since the tab character is unprintable, you can specify the value as a hexadecimal code, in this case '09'x. For z/OS, the code for a TAB character is '05'x.
4. Creating Raw Data Files Using the DATA Step 71
Specifying an Alternate Delimiter
This demonstration illustrates how to create raw data files with fields delimited by commas.
68
In this example, we're modifying the program we used in the previous demonstration so that a comma is used as a delimiter.
To do this, we just add the option DLM=',' to the FILE statement.
When we run the program, we see that each field is separated by a comma.
This is a standard comma-delimited file, which we could import into an application like Excel, but notice that it does not contain a header row with the variable names.
Writing Static Text
You can use PUT statements to write static text strings instead of, or in addition to, data values.
69
PUT'text';
PUT variable 'text';
PUT'text' variable : format. 'text';
To add column headers or other text in your output, you can simply include the text in quotation marks in your PUT statement.
You can write lines containing only text, or lines containing a combination of text and formatted or unformatted data values.
4. Creating Raw Data Files Using the DATA Step 73
Writing Static Text Strings
This demonstration illustrates using PUT statements to write static text to raw data files.
70
Let's look at an example of using PUT statements to add text strings to lines of output. We've taken our previous program and added a PUT statement to write out a line of text with the variable names separated by commas.
When we run the program, we see that the PUT statement did write out the line with the variable names, but it did so on every iteration of the DATA step. So, we're going to need to make another modification to our program to get what we want.
Using the _N_ Automatic Variable to Conditionally
Execute PUT Statements
The _N_ automatic variable is created by every DATA step.
Each time that the DATA step iterates past the DATA statement, _N_ is incremented by 1. Therefore, the value of _N_ represents the number of times that the DATA step iterated.
71 data _null_; set orion.sales; if _N_=1 then ... ... run;
To solve our problem, we want to write out the line of column headers only on the first execution of the DATA step. To accomplish this, we can use the automatic variable _N_, whose value represents the number of times that the DATA step has iterated. The _N_ variable is automatically created by SAS when the DATA step executes.
If we want to execute a statement only on the first iteration of the DATA step, we can use an IF statement that tests to see if _N_ is equal to 1.
4. Creating Raw Data Files Using the DATA Step 75
Writing Static Strings Conditionally
This demonstration illustrates using the _N_ automatic variable to conditionally write strings to a raw data file.
72
In this demonstration we'll see how using the _N_ automatic variable lets us execute a PUT statement conditionally to write out our column headers on the first iteration of the DATA step.
Starting with our program from the last demonstration, we just need to modify the PUT statement so that it is executed only if the value of _N_ is equal to 1.
When we run the program, the output shows that the header line is written only on the first iteration of the DATA step.
Creating Files with Fixed Columns
In some cases you might want to write raw data files with data in fixed columns. For example:
73
1241054779 3 24 02JAN2007
1241063739 1 89 03JAN2007
1241066216 1 171 04JAN2007
1241086052 3 53 06JAN2007
1241147641 1 53 13JAN2007
So far, all of our examples have shown how to create delimited files. There may be some times when you want to create a file with data in fixed columns, as shown here.
4. Creating Raw Data Files Using the DATA Step 77
Creating Files with Fixed Columns
To write data in fixed columns, use formatted outputwith your PUT statement:
74
PUT@pointer-value-1 variable-1 format-1.
@pointer-value-2 variable-2 format-2.
…
@pointer-value-n variable-n format-n1;
put @1 order_id 10. @12 order_type 3. @16 customer_id 3. @20 order_date date9.;
To write data in fixed columns, you can use a PUT statement with formatted output. With formatted output, you use a pointer control to tell SAS in what column to begin writing each field. The pointer control is an at sign (@), followed by the beginning column for the field. Next, you enter the variable name, and then the format that you want to use to write the data.
Creating Files with Fixed Columns
This demonstration illustrates using the formatted output with a PUT statement to create a file with data in fixed columns.
75
In this example, we'll show you how to used formatted output to create a file with data in fixed columns. To create the output, we code the PUT statement so that for each variable, we specify its starting column and format.
4. Creating Raw Data Files Using the DATA Step 79
Summary
Use FILE and PUT statements to create delimited or fixed-column files.
Begin a DATA step with a DATA _NULL_ statement when you create a raw data file.
Use list or modified list output with the PUT statement to create a delimited file.
Use FILE PRINT to preview the file.
Use static text and the _N_ variable to include a header row.
Use formatted output in PUT statements to create fixed column files.
76
Let's review what we've covered in this section.
• Like the methods we've covered before, you can use FILE and PUT statements to create delimited files. You can also use FILE and PUT to create fixed-column files.
• When creating a raw data file, you should begin the DATA step with a DATA _NULL_ statement.
• To create a raw file, use list or modified list output with the PUT statement.
• You can use a FILE PRINT statement to preview the file instead of writing the file directly.
• To include a header row in your output, add static text to your PUT statement and use the _N_ variable to control when the text is output.
• Finally, if you want to create fixed column files, use PUT statements with formatted output to write the data.
5. Summary
1. Overview
2. Creating Raw Data Files Using the EXPORT Procedure
3. Creating Raw Data Files Using the Output Delivery System
4. Creating Raw Data Files Using the DATA Step
5. Summary
Creating Raw Data Files Using SAS
77
In our final section, I'll review the pros and cons of each method and help you determine which method is best for your needs.
5. Summary 81
Considerations in Creating a Raw Data File
Format of the file – Delimited fields – Fixed column
Column headings
Titles/footnotes
78
When deciding which approach to use to create a raw data file, the first thing you need to consider is what format you want the file in. Your choices are a file with fields separated by delimiters or a file with fields in fixed columns.
Your next consideration is whether you want the variable names or labels included as column headings. Finally, you need to consider whether you want your file to include additional text, such as titles or footnotes.
Methods for Creating a Raw Data File
PROC EXPORT
ODS CSVALL
DATA Step with FILE and PUT Statements
79
The three methods that you can use to create raw data files are
• using PROC EXPORT
• using the ODS CSVALL statement
5. Summary 83
Delimited Files without Column Headers
PROC EXPORT with PUTNAMES=NO
80
Smith,1500.00,919-555-3467
Wilson,2700.65,248-555-8653
Williamson,4165.80,303-555-8721
Lee,3267.00,215-555-3189
Now let's review which method is most appropriate for each type of output.
If you want to create a delimited file without a column header row, your most convenient option is to use PROC EXPORT. Since PROC EXPORT automatically includes a column header row, you need to add a PUTNAMES=NO statement to eliminate the header row.
Delimited Files with Column Headers
PROC EXPORT
ODS CSVALL and PROC PRINT with no titles or footnotes
81
Name,Amount,Phone
Smith,1500.00,919-555-3467
Wilson,2700.65,248-555-8653
Williamson,4165.80,303-555-8721
"Name","Amount","Phone"
"Smith",1500.00,"919-555-3467"
"Wilson",2700.65,"248-555-8653"
"Williamson",4165.80,"303-555-8721"
To create delimited files with a row containing column headers, you can use either PROC EXPORT or the ODS CSVALL statement with PROC PRINT. If you use ODS CSVALL, do not include TITLE or FOOTNOTE statements. Note also that ODS CSVALL will include quotation marks around character strings.
5. Summary 85
Delimited Files with Column Headers
and Titles/Footnotes
ODS CSVALL with PROC PRINT and TITLE/FOOTNOTE statements
82
Low Volume Sales
"Order_ID","Order_Type","Customer_ID","Order_Date" 1241054779,3,24,"02JAN2007" 1241063739,1,89,"03JAN2007" 1241066216,1,171,"04JAN2007" 1241086052,3,53,"06JAN2007" 1241147641,1,53,"13JAN2007" First 2 Weeks of January
If you want to create a delimited file with column headers as well as title and footnote text, use the ODS CSVALL statement with PROC PRINT, and add TITLE and FOOTNOTE statements.
Fixed Column Files
DATA Step with FILE/PUT Statements
83
07JUL08 AX24 LAX DFW 1600 2010
07JUL08 RB104 DTW ORD 1350 1400
08JUL08 AX876 ATL BWI 900 1100
08JUL08 CZ12 CLE BOS 1005 1140
Finally, if you want to create a raw file with fields in fixed columns, use FILE and PUT statements in a DATA step.
5. Summary 87
More Information
SAS Help System – EXPORT Procedure – ODS CSVALL Statement – PUT Statement
– FSLIST Procedure
SAS e-Lecture: Creating DATA Step Reports with the Output Delivery System
84
This has been a general overview of the process of creating raw data files with SAS. For more information on creating raw files, see the SAS Help Facility and refer to the index entries for
• the EXPORT Procedure
• the ODS CSVALL Statement
• the PUT Statement
• and for the FSLIST Procedure.
For more information on creating output with the PUT statement, you can also view the SAS e-Lecture Creating DATA Step Reports with the Output Delivery System.
Credits
Creating Raw Data Files Using SAS®was developed by Mike Kalt.
Additional contributions were made by Michele Ensor, Mark Jordan, Kathy Passarella, and Cynthia Zender.
85
This concludes the SAS e-Lecture Creating Raw Data Files Using SAS®. I hope you've found the material in this lecture to be helpful for your work tasks.
5. Summary 89
Comments?
We would like to hear what you think.
Do you have any comments about this lecture?
Did you find the information in this lecture useful?
What other e-lectures would you like SAS to develop in the future? Please e-mail your comments to
[email protected]
Or you can fill out the short evaluation form at the end of this lecture. 86
If you have any comments about this lecture or e-lectures in general, we would appreciate receiving your input. You can use the e-mail address listed here to provide that feedback.
Copyright
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries.
® indicates USA registration. Other brand and product names are trademarks of their respective companies.
Copyright © 2009 by SAS Institute Inc., Cary, NC 27513, USA. All rights reserved.
87
