I'm trying to use ODS EXCEL to do the following:
1. Print something, and put that on a sheet named something specific.
2. Run a PROC COMPARE, and make two sheets, the main Dataset sheet and the Variables comparison, and name them something intelligent.
Either of these is not a problem separately; I like the automatic names you get from SAS by default for 2, and for 1 I know how to use SHEET_NAME. But when I do them - in this order - it fails.
data class;
set sashelp.class;
drop name;
run;
ods excel file="e:\temp\test.xlsx" options(sheet_name="test" sheet_interval="table");
proc print data=sashelp.class;
run;
ods exclude compareSummary;
title1 "STEP 1: Compare contents of files";
proc compare base=sashelp.class compare=class listvar novalues ;
run;
ods excel close;
I end up with "test 2" and "test 3" as sheet names, which is no good. I can use
options(sheet_name=' ')
and get "Sheet 1" and "Sheet 2", which is not really that much better. If I use Sheet_Label I don't get much better; obviously something sees that sheet_name has _ever_ been set, and refuses to forget that it was.
I have a workaround using PROC DOCUMENT that works fine, but I'm curious if there's a more integrated way to do this. Something like ODS PROCLABEL but for the tables maybe?
Thanks.
data class;
set sashelp.class;
drop name;
run;
ods excel file="e:\temp\test.xlsx" options(sheet_name="test" sheet_interval="table");
proc print data=sashelp.class;
run;
ods document name=conts;
ods exclude compareSummary;
ods excel exclude all;
title1 "STEP 1: Compare contents of files";
proc compare base=sashelp.class compare=class listvar novalues ;
run;
ods document close;
ods exclude none;
proc document name=conts;
ods excel options(sheet_name="Compare Datasets");
replay \Compare#1\CompareDatasets#1;
run;
ods excel options(sheet_name="Compare Variables");
replay \Compare#1\CompareVariables#1;
run;
quit;
ods excel close;
See if this gives you a hint:
ods excel file="e:\temp\test.xlsx" options(sheet_name="Print" sheet_interval="table"); proc print data=sashelp.class; run; ods exclude compareSummary; ods excel options(sheet_name="Compare" ); title1 "STEP 1: Compare contents of files"; proc compare base=sashelp.class compare=class listvar novalues ; run; ods excel close;
Not sure what it's supposed to be hinting at? That produces "Compare" and "Compare 2" as sheet names, which isn't what I want; I want to individually name those two tabs.
This is a way to do it without using PROC DOCUMENT.
data class;
set sashelp.class;
drop name;
run;
ods excel file="e:\temp\test.xlsx" options(sheet_name="test" sheet_interval="proc");
proc print data=sashelp.class;
run;
ods excel options(sheet_name="Compare Datasets" sheet_interval="table");
ods select compareDatasets;
title1 "STEP 1: Compare contents of files";
proc compare base=sashelp.class compare=class listvar novalues;
run;
ods excel options(sheet_name="Compare Variables");
ods select compareVariables;
proc compare base=sashelp.class compare=class listvar novalues;
run;
ods excel close;
Thanks - that was a consideration as well, trying to avoid two PROC COMPAREs of course but since we're not doing value comparisons it's not exactly expensive. Thanks for the suggestion!
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.