Lesson 7, Part 3: Creating Excel Worksheets from SAS Output¶
How to produce two Excel worksheets containing the SAS output?¶
Program Purpose
The program creates an Excel workbook named class_classfit.xlsx containing:
Worksheet CLASS — Variable information for SASHELP.CLASS Worksheet CLASSFIT — Variable information for SASHELP.CLASSFIT
Only the Variables table from PROC CONTENTS is written to the workbook.
The 'PROC' option creates a new Excel worksheet for each SAS procedure.
In [13]:
ods _all_ close;
options nodate nonumber nosource;
%let Path = C:\Explore\SAS\Lesson7\Lesson7Data;
ods excel
file="&Path.\Twosheets_class_classfit.xlsx"
options(
embedded_titles="yes"
sheet_interval="PROC"
);
/*-----------------------------------------------------------*/
/* SASHELP.CLASS */
/*-----------------------------------------------------------*/
ods excel options(sheet_name="CLASS");
ods select Variables;
title "PROC CONTENTS - SASHELP.CLASS";
proc contents data=sashelp.class position;
run;
/*-----------------------------------------------------------*/
/* SASHELP.CLASSFIT */
/*-----------------------------------------------------------*/
ods excel options(sheet_name="CLASSFIT");
ods select Variables;
title "PROC CONTENTS - SASHELP.CLASSFIT";
proc contents data=sashelp.classfit position;
run;
/* Restore the default ODS selection and exclusion lists */
ods select all;
ods excel close;
title;
The SAS System NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.05 seconds cpu time 0.04 seconds NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.03 seconds cpu time 0.03 seconds NOTE: Writing EXCEL file: C:\Explore\SAS\Lesson7\Lesson7Data\Twosheets_class_classfit.xlsx The SAS System E3969440A681A2408885998500000015
How to write multiple SAS outputs to the same Excel worksheet.¶
sheet_interval="none" tells ODS EXCEL not to create a new worksheet after each procedure. As a result:
PROC CONTENTS for SASHELP.CLASS PROC CONTENTS for SASHELP.CLASSFIT
will both be written to the same worksheet, one below the other.
In [17]:
ods _all_ close;
options nodate nonumber nosource;
%let Path = C:\Explore\SAS\Lesson7\Lesson7Data;
ods excel
file="&Path.\OneSheet_class_classfit.xlsx"
options(
embedded_titles="yes"
sheet_interval="proc"
);
ods select Variables;
ods excel options(sheet_name="CLASS");
title "PROC CONTENTS - SASHELP.CLASS";
proc contents data=sashelp.class position;
run;
ods excel options(sheet_name="CLASSFIT");
title "PROC CONTENTS - SASHELP.CLASSFIT";
proc contents data=sashelp.classfit position;
run;
ods select all;
ods excel close;
title;
The SAS System NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.04 seconds cpu time 0.04 seconds NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.09 seconds cpu time 0.09 seconds NOTE: Writing EXCEL file: C:\Explore\SAS\Lesson7\Lesson7Data\OneSheet_class_classfit.xlsx The SAS System E3969440A681A2408885998500000019
How to create multiple Excel worksheets containing SAS output¶
In [19]:
ods _all_ close;
/* ods listing close; */
options nodate nonumber nosource;
ods excel file="C:\Explore\SAS\Lesson7\Lesson7Data\TwoOutputPerSheet.xlsx"
options(embedded_titles='yes' embedded_footnotes='yes'
title_footnote_width="80"); /* Open an Excel destination */
/* Sheet 1 will include two tables */
/* This sheet is named "Heart_Cars" and prints two tables */
ods excel options(sheet_name="Heart_Cars" sheet_interval="none");
ods escapechar='^';
ods text="^S={fontweight=bold fontsize=12pt}
Table 1: SASHELP.HEART data set's variable attributes";
proc print data=sashelp.vcolumn label;
var name type format length label;
where libname='SASHELP' and memname='HEART';
run;
ods text="^S={fontweight=bold fontsize=12pt}
Table 2: SASHELP.CARS data set's variable attributes";
proc print data=sashelp.vcolumn label;
var name type format length label;
where libname='SASHELP' and memname='CARS';
run;
/* Move to new sheet without printing a table */
ods exclude all;
ods excel options(sheet_interval="proc");
proc print data=sashelp.class; run;
ods exclude none;
/* Sheet 2 */
/* The sheet is named "Demographics_IRIS" and prints two more tables */
ods excel options(sheet_interval="now" sheet_name="Demographics_IRIS");
ods excel options(sheet_interval="none");
proc odstext;
p "^{style[fontweight=bold fontsize=12pt color=blue] Table 3: SASHELP.DEMOGRAPHICS data set's variable attributes";
run;
proc print data=sashelp.vcolumn label;
var name type format length label;
where libname='SASHELP' and memname='DEMOGRAPHICS';
run;
ods text="^S={fontweight=bold fontsize=12pt}
Table 4: SASHELP.IRIS data set's variable attributes";
proc print data=sashelp.vcolumn label;
var name type format length label;
where libname='SASHELP' and memname='IRIS';
run;
ods excel close;
title;
/* ods listing;*/
The SAS System NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 NOTE: There were 17 observations read from the data set SASHELP.VCOLUMN. WHERE (libname='SASHELP') and (memname='HEART'); NOTE: PROCEDURE PRINT used (Total process time): real time 0.16 seconds cpu time 0.07 seconds NOTE: There were 15 observations read from the data set SASHELP.VCOLUMN. WHERE (libname='SASHELP') and (memname='CARS'); NOTE: PROCEDURE PRINT used (Total process time): real time 0.11 seconds cpu time 0.06 seconds NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: PROCEDURE PRINT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: PROCEDURE ODSTEXT used (Total process time): real time 0.05 seconds cpu time 0.01 seconds NOTE: There were 18 observations read from the data set SASHELP.VCOLUMN. WHERE (libname='SASHELP') and (memname='DEMOGRAPHICS'); NOTE: PROCEDURE PRINT used (Total process time): real time 0.07 seconds cpu time 0.07 seconds NOTE: There were 5 observations read from the data set SASHELP.VCOLUMN. WHERE (libname='SASHELP') and (memname='IRIS'); NOTE: PROCEDURE PRINT used (Total process time): real time 0.02 seconds cpu time 0.03 seconds NOTE: Writing EXCEL file: C:\Explore\SAS\Lesson7\Lesson7Data\TwoOutputPerSheet.xlsx The SAS System E3969440A681A2408885998500000021
How to create a file list using SAS¶
In [33]:
options nodate nonumber nosource;
%let Path = C:\Explore\SAS\Lesson7\Lesson7Data;
Filename filelist pipe "dir /b /s &Path.\*.xlsx";
Data _null_;
Infile filelist truncover;
Input filename $100.;
Put filename=;
Run;
The SAS System NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 NOTE: The infile FILELIST is: Unnamed Pipe Access Device, PROCESS=dir /b /s C:\Explore\SAS\Lesson7\Lesson7Data\*.xlsx, RECFM=V,LRECL=32767 filename=C:\Explore\SAS\Lesson7\Lesson7Data\OneSheet_class_classfit.xlsx filename=C:\Explore\SAS\Lesson7\Lesson7Data\TwoOutputPerSheet.xlsx filename=C:\Explore\SAS\Lesson7\Lesson7Data\Twosheets_class_classfit.xlsx NOTE: 3 records were read from the infile FILELIST. The minimum record length was 57. The maximum record length was 64. NOTE: DATA statement used (Total process time): real time 0.26 seconds cpu time 0.00 seconds The SAS System E3969440A681A2408885998500000035
How to list all files with the extension SAS?¶
In [35]:
options nodate nonumber nosource;
%let Path = C:\Explore\SAS\Lesson7\Lesson7Data;
filename filelist pipe "dir /b /s ""&Path.\*.xlsx""";
data filelist;
length filename $500;
infile filelist truncover length=reclen;
input filename $varying500. reclen;
run;
proc print data=filelist;
var filename;
run;
| Obs | filename |
|---|---|
| 1 | C:\Explore\SAS\Lesson7\Lesson7Data\OneSheet_class_classfit.xlsx |
| 2 | C:\Explore\SAS\Lesson7\Lesson7Data\TwoOutputPerSheet.xlsx |
| 3 | C:\Explore\SAS\Lesson7\Lesson7Data\Twosheets_class_classfit.xlsx |