Tag: label

It is common to export SAS data tables or outputs to Excel spreadsheets.  The default Excel output will only contain field names in the first row of the spreadsheet.  The ‘label’ option will allow the SAS label names to be the column names on the first row of the exported table.

Method 1:

PROC EXPORT DATA=table1.
OUTFILE= “C:\Work\SAS\table1.xlsx”
label DBMS=EXCEL REPLACE;
SHEET=”table1″;
NEWFILE=YES;
RUN;

method 2:

libname excelout “X:\Work\excelfile.xlsx”;

data excelout.sheet1 (dblabel=yes) ;
set tablename;

run;

libname excelout clear;

9.4 SAS Difference

PROC EXPORT

• Experience slowness in running PROC IMPORT and PROC EXPORT
• Find reference: https://blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/;
• Change: DBMS= EXCEL to DBMS=EXCELCS

PROC EXPORT DATA= SASHELP.CARS 
OUTFILE= "C:\Excel\car.xlsx" 
            DBMS=EXCELCS REPLACE;
     SHEET="car"; 
RUN;

• Bug: a blank tab (_SAS_empty_) with A1 = “_empty_(CHECK_OTHER_SHEET) was generated in the xlsx file.

Solution: https://communities.sas.com/t5/SAS-Programming/Proc-Export-creating-a-blank-SAS-empty-sheet-in-the-exported/td-p/483623

• Change DBMS=EXCEL to DBMS=XLSX and the blank tab will disappear.

PROC EXPORT DATA= SASHELP.CARS 
OUTFILE= "C:\Excel\car.xlsx" 
            DBMS=XLSX REPLACE;
     SHEET="car"; 
RUN;

Alternative 1: LIBNAME method

• Add xlsx in the libname statement;
• dblabel = option not work for the xlsx engine.

Libname exl xlsx "C:\Excel\car.xlsx";
data exl.cars;
set SASHELP.CARS ;
run;
libname exl clear;

Alternative 2: ODS EXCEL method

• ods excel; ods excel close;
• file =” “;
• ods excel options (sheet_name = ” “);
• proc print noobs label data=;run;

ods excel file= "C:\Excel\&fac._research.xlsx";
ods excel options (sheet_name = "&fac.");
proc print noobs label data =research; 
where faculty = "&fac.";
run; 
ods excel close;