Writing out Data Using SAS

Just as there are many ways to read data into SAS, there are also many ways to write out data. Some of the more common ways of writing out raw data using SAS commands are illustrated in this chapter. Writing out data using the Export Wizard is also described.

If you use SAS commands to write data, you will have more control over how the data are written, but the variable names will not be included in the output file. If you use the Export Wizard, SAS will write the variable names in the first row, however, you will have less control over the way the variables are written.

Create the data set to use for examples:

We first submit a libname statement to tell SAS the location of the folder containing the SAS dataset that will be used in this example. You will need to point SAS to the correct folder on your computer . We create a temporary SAS dataset from the permanent dataset using a set statement.

libname sasdata2 "c:\temp\sasdata2";

data iris;

set sasdata2.iris;

run;

The SAS log from these commands is shown below. Note that the dataset IRIS has 150 observations.

1 libname sasdata2 "c:\temp\sasdata2";

NOTE: Directory for library SASDATA2 contains files of mixed engine types.

NOTE: Libref SASDATA2 was successfully assigned as follows:

Engine: V9

Physical Name: c:\temp\sasdata2

2 data iris;

3 set sasdata2.iris;

4 run;

NOTE: There were 150 observations read from the data set SASDATA2.IRIS.

NOTE: The data set WORK.IRIS has 150 observations and 5 variables.

Write out raw data separated by blanks:

The data step below processes the IRIS dataset, but no new dataset is created ( _null_ is used in place of a dataset name). Using data _null_ we can create new variables and carry out data manipulations and modifications as in an ordinary data step, but we save space and resources by not creating a new dataset. We use the file statement, and put statement to write an ascii file called iris.dat. Note that the output file will not contain the variable names.

/*WRITE RAW DATA SEPARATED BY BLANKS*/

data _null_;

set iris;

file "c:\temp\iris.dat";

put sepallen

sepalwid

petallen

petalwid

species;run;

We can check the log to see what was done. The ascii file, iris.dat, contains the raw data from this SAS data set, and no variable names.

5 data _null_;

6 set iris;

7 file "c:\temp\iris.dat";

8 put sepallen

9 sepalwid

10 petallen

11 petalwid

12 species;

13 run;

NOTE: The file "c:\temp\iris.dat" is:

File Name=c:\temp\iris.dat,

RECFM=V,LRECL=256

NOTE: 150 records were written to the file "c:\temp\iris.dat".

The minimum record length was 17.

The maximum record length was 22.

NOTE: There were 150 observations read from the data set WORK.IRIS.

Alternatively, you can use the Export Wizard and select the file type to save as “Delimited File”. This will save the data with the variable names in the first row.

Write out raw data separated by tabs:

We again use a data _null_ statement to process the data before writing it out, without creating a new SAS data set. In the file statement we specify the dlm=”09”X option (dlm means delimiter, “09” is the hexadecimal specification for a tab, and the X means the information in quotes is a hexadecimal specification) to tell SAS to separate the values of the variables by tabs. If you use SAS commands as shown below, the variable names will not be part of the output file.

/*WRITE RAW DATA SEPARATED BY TABS*/

data _null_;

set iris;

file "c:\temp\iris.txt" dlm="09"X;

put sepallen

sepalwid

petallen

petalwid

species;

run;

Alternatively, you can use the Export Wizard and select the file type to save as “Tab Delimited File (*.txt)”. This will save the data with the variable names in the first row.

Write out raw data separated by commas (.csv file):

We again use a data _null_ step to process the data before writing it out. In this case we specify dlm=”,” in the file statement to tell SAS to separate the value for each variable by a comma. This will save the raw data into a file; the variable names will not be included. .

/*WRITE RAW DATA SEPARATED BY COMMAS*/

data _null_;

set iris;

file "c:\temp\iris.csv" dlm=",";

put sepallen

sepalwid

petallen

petalwid

species;run;

Alternatively, you can use the Export Wizard and select the file type to save as “Comma Separated values (*.csv)”. This will save the data with the variable names in the first row.

Write out raw data in column locations:

We again use a data _null_ step to process the data before writing it out. In this case we use a simple file statement with no options, but specify the column range for each variable in the put statement. Again, these commands will not write out the variable names in the output file.

/*WRITE RAW DATA INTO SPECIFIED COLUMNS*/

data _null_;

set iris;

file "c:\temp\iris_column.dat";

put species 1-10

sepallen 12-15

sepalwid 17-20

petallen 22-25

petalwid 27-30;

run;

SAS has a method called EFI (External File Interface) to write out variables in particular columns. This is accessible through the Export Wizard, but is not recommended, as it is rather difficult to use.

A shortcut way to write out raw data in column locations:

Rather than specifying the column range for each variable individually, we can specify a variable list, followed by the format to be used for all variables in the list, as shown below.

/*A SHORTCUT WAY TO WRITE OUT DATA IN COLUMN LOCATIONS*/

data _null_;

set iris;

file "c:\temp\iris_column2.dat" ;

put (sepallen sepalwid petallen petalwid) (4.) @18 species $10.;

run;

The put statement tells SAS to use the same numeric format, 4 columns wide with no places after the decimal (4.), for each of the numeric variables (a format of w. is assumed to mean w.0, i.e., no places after the decimal). Beginning at column 18 the character variable, SPECIES, is to be written using a width of 10 columns ($10.). Note that when formats are specified, they need to include a decimal, in the form w.d (width.decimals) for numeric variables and $w. or equivalently, $charw., for character variables.

Export data to Excel:

Just as datasets can be imported from Excel and other database management systems using the SAS Import Wizard, they can be exported using the Export Wizard. The process is described below:

Go to the File menu, choose Export Data…, and in the dialog box that comes up, choose the Library and the Member (i.e., dataset) to export (in this case WORK is the library and IRIS is the member). Click Next> and in the next dialog box select the type of file that you wish to export from the drop-down list (in this case, “Excel 97, 2000, or 2002 Workbook”), click Next>. In the Connect to MS Excel dialog box, browse to the location where you wish to save the Excel file, type a name for the Excel file to save in the dialog box, and click OK. In the next dialog box type a name for the sheet that you are exporting (often it will be Sheet1) and click Next>. If you wish to save a command file that you can use later, Browse to the location where you want to save your command file (this will be called something like export_iris.sas), and click on Finish.

To recall the SAS commands for Exporting the data, go to the File…menu and select Open Program… Browse to the location where you saved your SAS commands and open them in the Program Editor Window.

This is the process that was used to create the SAS commands shown below.

PROC EXPORT DATA= WORK.IRIS

OUTFILE= "C:\temp\iris.xls"

DBMS=EXCEL REPLACE;

SHEET="sheet1";

RUN;