Tools of the Trade IV: Using SAS to Output the Data

We will be using SAS to output data in different formats.

Output to a text file
This is the most basic and popular way to output data from a SAS program. The code below will create the header for your dataset:

Data _null_;
file "c:\Item_List.txt" lrecl = 150;
put
@1 "Product"
@10 "Type"
@20 "Service"
@30 "Price";
run;

This code will let you output the contents of your dataset:

Data _null_;
file "c:\Item_List.txt" mod lrecl = 150;
set ItemList;
put
@1 product
@10 type
@20 service
@30 price;
run;

To directly output a SAS dataset into a spaced tab text file, use the following procedure:

PROC EXPORT DATA= WORK.ItemList
OUTFILE= "C\Item_List.txt"
DBMS=TAB REPLACE;
RUN;

To output a SAS dataset into a delimited text file, use the following procedure:

PROC EXPORT DATA= WORK.ItemList
OUTFILE= "C:\Item_List"
DBMS=DLM REPLACE;
DELIMITER='00'x;
RUN;

Output data to *.csv file
To output data to a *.csv file, use the following procedure:

PROC EXPORT DATA= WORK.ItemList
OUTFILE= "C:\Item_List.csv"
DBMS=CSV REPLACE;
RUN;

Output data to an Excel file
There are several ways to output SAS datasets to an Excel spreadsheet. The following code will dump the data into your Excel spreadsheet, but you'll need to keep the spreadsheet open:

filename toexcel dde "excel|Sheet1!r2c1:r30000c11";
data _null_;
file toexcel dlm='09'x notab;
set Final_Data;
put Date Day Train Orig Dest Pallets Footage;
run;

This SAS macro allows you more flexibility to output your data. You can specify the columns and rows of your spreadsheet where you want to output the data:

%macro m_xlout(sheet, tbl, ds, var, row1, col1, row2, col2);

filename tmp dde "excel|[&sheet.]:&tbl.!r&row1.c&col1.:r&row2.c&col2.";
data _null_;
set &ds.;
file tmp;
put &var.;
run;

%mend m_xlout;
%m_xlout(UtilizationRates.xls, Utilization Details, facilities, route trip orig dest arrivl utiliz, 5, 2, 16000, 7);

Finally, if you just want to dump the entire SAS dataset into an Excel spreadsheet, use the following procedure:

PROC EXPORT DATA= Final_Data
OUTFILE= "C:\Final Data.xls"
DBMS=EXCEL2000 REPLACE;
RUN;

Output data in HTML
Here is a quick and dirty way to output SAS data in HTML format:

filename odsout "C:\Outputs\Analytics Dashboards"
ods html path=odsout
body="Dashboard reports.html"
nogtitle;

proc summary data=indat;
var revenue;
output out=outdat sum=;
proc print;
title "Summary of Premier Transactions";
run;

ods html close;
ods listing;
title;

The printouts between ods html path and ods html close statements will appear in an HTML page in the Analytics Dashboard folder in the C:\ drive.