Besides RTF file, SAS programmer also need to produce excel file. Today’s post will present several special examples including how to create empty worksheet, how to highlight worksheet cells or how to add hyperlinks into worksheet cells using ODS EXCEL.
Example Data
First of all, let’s use SASHELP.CLASS to create a dummy demographic dataset which will be used later.
data dm; length subjid $20.; set sashelp.class; subjid = "010"||strip(put(_n_,z2.)); run;
Simple Example
Now let’s use example dataset DM to create a simple workbook. Worksheet name is “Class”.
options device=png;
ods _all_ close;
ods excel file="&protpath/reports/dm.xlsx" style=excel;
options missing="";
ods excel options(autofilter='yes' sheet_name="Class"
embedded_titles='yes' embedded_footnotes='yes'
sheet_interval='PROC' absolute_column_width="10, 10, 10, 10, 10, 10"
flow="TABLES") ;
title1 j=c "Listing 1 Demographic Data";
footnote1 j=l "Data Extraction Date: 01Jan2019";
proc report data = dm nowindows missing split='~' spanrows
style(column)=[vjust=top]
style(header)=[vjust=top backgroundcolor=cxEDF2F9 color=cx112277 fontweight=bold just=center];
columns subjid name sex age height weight;
run;
ods excel close;
ods listing;
Options embedded_titles and embedded_footnotes should be populated as “Yes” in order to print out title and footnote. Column width can be defined via option absolute_column_width. Column width should be separated by ‘, ‘. Here is the output.
Pass Text Format via TAGATTR =
If you observe closely, you will find that first ‘0’ in column SUBJID is missing. That is because SAS did not define any number format and current number format for column A is ‘General’. To eliminate this kind of error, it is better to pass text format using TAGATTR = in Define statement for all character variables.
options device=png;
ods _all_ close;
ods excel file="&protpath/reports/dm.xlsx" style=excel;
options missing="";
ods excel options(autofilter='yes' sheet_name="Class"
embedded_titles='yes' embedded_footnotes='yes'
sheet_interval='PROC' absolute_column_width="15, 10, 10, 10, 10, 10"
flow="TABLES") ;
title1 j=c "Listing 1 Demographic Data";
footnote1 j=l "Data Extraction Date: 01Jan2019";
proc report data = dm nowindows missing split='~' spanrows
style(column)=[vjust=top]
style(header)=[vjust=top backgroundcolor=cxEDF2F9 color=cx112277 fontweight=bold just=center];
columns subjid name sex age height weight;
define subjid/ style(column)=[tagattr='type:String'];
run;
ods excel close;
ods listing;
Empty Worksheet
Below code shows how to create a blank worksheet for the situation where the source dataset does not contain any observations.
data dm;
set dm;
if _n_ >= 1 then delete;
subjid = "010"||strip(put(_n_,z2.));
label subjid = "Subjid"
name = "Name"
sex = "Sex"
age = "Age"
height = "Height"
weight = "Weight"
;
run;
proc transpose data=dm out=dm;
var _all_;
run;
proc transpose data=dm out=dm(drop=_name_ _label_);
var _label_;
run;
options device=png;
ods _all_ close;
ods excel file="&protpath/reports/dm.xlsx" style=excel;
options missing="";
ods excel options(autofilter='yes' sheet_name="Class"
embedded_titles='no' embedded_footnotes='no'
sheet_interval='PROC' absolute_column_width="15, 10, 10, 10, 10, 10"
flow="TABLES") ;
proc report data = dm nowindows missing split='~' noheader
style(column)=[vjust=top backgroundcolor=cxEDF2F9 color=cx112277 fontweight=bold just=center]
style(header)=[vjust=top backgroundcolor=cxEDF2F9 color=cx112277 fontweight=bold just=center];
columns subjid name sex age height weight;
define subjid/ style(column)=[tagattr='type:String'];
run;
ods excel close;
ods listing;
Empty Worksheet Stating No Data Available
Following code shows how to display “No Data Meeting Selected Criteria” in empty worksheet.
data dm;
subjid = " ";
name = " ";
sex = " ";
age = " ";
height = " ";;
weight = " ";
mx_grpn = 1; output;
mx_grpn = 2; output;
run;
proc format;
value mx_grp
1 = "No data meeting selected criteria"
2 = " "
;
run;
options device=png;
ods _all_ close;
ods excel file="&protpath/reports/dm.xlsx" style=excel;
options missing="";
ods excel options(autofilter='yes' sheet_name="Class"
embedded_titles='no' embedded_footnotes='no'
sheet_interval='PROC' absolute_column_width="15, 10, 10, 10, 10, 10"
flow="TABLES") ;
proc report data = dm nowindows missing split='~'
style(column)=[vjust=top backgroundcolor=cxEDF2F9 just=center]
style(header)=[vjust=top backgroundcolor=cxEDF2F9 color=cx112277 fontweight=bold just=center];
columns mx_grpn subjid name sex age height weight;
define mx_grpn/ noprint order;
compute before ;
line " ";
endcomp;
compute after mx_grpn;
line mx_grpn mx_grp.;
endcomp;
run;
ods excel close;
ods listing;
Highlight Worksheet Cells Meeting Criteria
To enable call define statement work, weight must be declared in define statement before. If you remove ‘define weight/display;’ from below code, no observation will be highlighted in lightblue. Any variables used in if statement within compute block should be declared in define statement before compute block.
Case 1
data dm;
length subjid $20.;
set sashelp.class;
subjid = "010"||strip(put(_n_,z2.));
run;
options device=png;
ods _all_ close;
ods excel file="&protpath/reports/dm.xlsx" style=excel;
options missing="";
ods excel options(autofilter='yes' sheet_name="Class"
embedded_titles='no' embedded_footnotes='no'
sheet_interval='PROC' absolute_column_width="15, 10, 10, 10, 10, 10"
flow="TABLES") ;
proc report data = dm nowindows missing split='~'
style(column)=[vjust=top just=center]
style(header)=[vjust=top backgroundcolor=cxEDF2F9 color=cx112277 fontweight=bold just=center];
columns subjid name sex age height weight;
define subjid/ style(column)=[tagattr='type:String'];
define weight/display;
compute weight;
if 0 < weight < 90 then
call define(_row_, "style", "style=[backgroundcolor=lightblue]");
endcomp;
run;
ods excel close;
ods listing;
Case 2
data dm;
length subjid $20.;
set sashelp.class;
subjid = "010"||strip(put(_n_,z2.));
run;
options device=png;
ods _all_ close;
ods excel file="&protpath/reports/dm.xlsx" style=excel;
options missing="";
ods excel options(autofilter='yes' sheet_name="Class"
embedded_titles='no' embedded_footnotes='no'
sheet_interval='PROC' absolute_column_width="15, 10, 10, 10, 10, 10"
flow="TABLES") ;
proc report data = dm nowindows missing split='~'
style(column)=[vjust=top just=center]
style(header)=[vjust=top backgroundcolor=cxEDF2F9 color=cx112277 fontweight=bold just=center];
columns subjid name sex age height weight;
define subjid/ style(column)=[tagattr='type:String'];
define height/display;
compute weight;
if 0 < height < 60 then
call define(_row_, "style", "style=[backgroundcolor=lightblue]");
endcomp;
run;
ods excel close;
ods listing;
In this example, if you add ‘define height/noprint;’ after ‘endcomp;’, height will not be displayed while rows in which height is less than 60 will still be highlighted.
Case 3
If we replace “_row_” with “_col_” in above call define statement, below output will be created. Only cells in column F will be highlighted.
Add Hyperlink to Another Worksheet
data dm;
length subjid $20.;
set sashelp.class;
subjid = "010"||strip(put(_n_,z2.));
dest = _n_ + 1;
run;
options device=png;
ods _all_ close;
ods excel file="&protpath/reports/dm.xlsx" style=excel;
options missing="";
ods excel options(autofilter='yes' sheet_name="subjid"
embedded_titles='no' embedded_footnotes='no'
sheet_interval='PROC' absolute_column_width="15, 10, 10, 10, 10, 10"
flow="TABLES") ;
proc report data = dm nowindows missing split='~'
style(column)=[vjust=top just=center]
style(header)=[vjust=top backgroundcolor=cxEDF2F9 color=cx112277 fontweight=bold just=center];
columns dest subjid;
define subjid/ style(column)=[tagattr='type:String'];
define dest/display;
compute subjid;
urlstring = "#'demographic'!A"||strip(put(dest,best.));
call define(_col_, 'URL', urlstring);
call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;
define dest/noprint;
run;
ods excel options(autofilter='yes' sheet_name="demographic"
embedded_titles='no' embedded_footnotes='no'
sheet_interval='PROC' absolute_column_width="15, 10, 10, 10, 10, 10"
flow="TABLES") ;
proc report data = dm nowindows missing split='~'
style(column)=[vjust=top just=center]
style(header)=[vjust=top backgroundcolor=cxEDF2F9 color=cx112277 fontweight=bold just=center];
columns subjid name sex age height weight;
define subjid/ style(column)=[tagattr='type:String'];
run;
ods excel close;
ods listing;
Above method can be used to generate a check log summary like below. You can have a try by yourself.
