9.6 Exporting SAS Data Sets with PROC EXPORT & the XLSX Engine

9.6 Exporting SAS Data Sets with PROC EXPORT & the XLSX Engine

Quick Answer: The final objective in the Generate Reports and Output area is Export data, with two expanded bullets: create a simple raw data file using PROC EXPORT as an alternative to the DATA step (controlling the format with the DBMS identifiers csv, jmp, tab, dlm), and export data to Microsoft Excel using the SAS/ACCESS XLSX engine.

PROC IMPORT reads external files in; PROC EXPORT writes SAS data sets out. Both infer as much as they can so that you never write an INPUT or PUT statement.

---

1. PROC EXPORT Syntax

PROC EXPORT DATA = libref.SAS-data-set <(data-set-options)>
            OUTFILE = "physical-file-path"      /* for delimited/raw files */
          | OUTTABLE = "table-name"             /* for database/workbook tables */
            DBMS = identifier
            <REPLACE>;
    <PUTNAMES = YES | NO;>
    <DELIMITER = 'character';>
    <SHEET = "worksheet-name";>
    <LABEL;>
RUN;

Exam Trap: omitting REPLACE when the target file already exists does not silently overwrite. SAS writes ERROR: File already exists and produces nothing. Expect at least one distractor built on this.

---

2. The DBMS Identifiers Named by the Content Guide

/* 1. Comma-separated values */
proc export data=work.final_report
            outfile="C:\Reports\2026_Q1_Summary.csv"
            dbms=csv
            replace;
    putnames=yes;      /* variable names in row 1 - the default */
run;

/* 2. Tab-delimited text */
proc export data=work.final_report
            outfile="C:\Reports\2026_Q1_Summary.txt"
            dbms=tab
            replace;
run;

/* 3. Pipe-delimited text */
proc export data=work.final_report
            outfile="C:\Reports\2026_Q1_Summary.psv"
            dbms=dlm
            replace;
    delimiter='|';
run;

/* 4. A JMP data table */
proc export data=work.final_report
            outfile="C:\Reports\2026_Q1_Summary.jmp"
            dbms=jmp
            replace;
run;

/* 5. An Excel workbook with a named worksheet */
proc export data=work.final_report
            outfile="C:\Reports\2026_Q1_Summary.xlsx"
            dbms=xlsx
            replace;
    sheet="Q1 Summary";
run;

PUTNAMES= and LABEL

Exam Trap: PUTNAMES= belongs to PROC EXPORT and GETNAMES= belongs to PROC IMPORT. The pairing is easy to remember from the direction of travel: you get names when reading in and put names when writing out.

Exporting a Subset

Because DATA= accepts data set options, no intermediate DATA step is needed:

proc export data=work.orders(where=(Region='West') keep=OrderID Amount OrderDate)
            outfile="C:\Reports\west_orders.csv"
            dbms=csv
            replace;
run;

---

3. Exporting to Excel with the SAS/ACCESS XLSX Engine

The second expanded bullet asks specifically for the XLSX engine, which is a different mechanism from DBMS=XLSX. A LIBNAME statement with the XLSX engine treats a workbook as a SAS library: each worksheet becomes a member you can read or write with ordinary SAS code.

/* Assign the workbook as a library */
libname xlout xlsx "C:\Reports\CompanyData.xlsx";

/* Each DATA step creates or replaces a worksheet */
data xlout.Q1_Summary;
   set work.final_report;
run;

data xlout.Q1_Detail;
   set work.orders;
run;

/* PROC COPY writes several sheets at once */
proc copy in=work out=xlout;
   select regional_totals product_mix;
run;

/* Clearing the libref closes the workbook and releases the file lock */
libname xlout clear;

Exam Trap: the XLSX engine writes raw data tables, not formatted reports. Titles, footnotes, and style templates are ignored. When a question mentions colours, embedded titles, or multiple tabs of formatted report output, the answer is ODS EXCEL, not the XLSX engine.

Always clear the libref. Until LIBNAME xlout CLEAR; executes, SAS holds the workbook open and Excel cannot.

---

4. The DATA Step Alternative

PROC EXPORT is described in the content guide as an alternative to the DATA step, which implies knowing what it replaces. A DATA _NULL_ step with FILE and PUT gives complete control over the layout:

data _null_;
   set work.final_report;
   file "C:\Reports\manual_export.csv" dlm=',' dsd;
   if _n_ = 1 then put "OrderID,Region,Amount";     /* hand-written header */
   put OrderID Region Amount;
run;

Choose PROC EXPORT for standard delimited and Excel output, and the DATA step when the receiving system demands an exact layout.