You're right on the money, I'm running 9.4 SP 2.
I have however written a work around that actually solves a secondary problem with blank sheets. First, when you use a blank sheet, the sheet created doesn't even contain headers to show what columns should be there. The code I've put together may not be the most elegant, but it is effective. This code reads in the metadata of an empty table into a single row of a new table with mock column names. Using Proc Report, I hide the headers tricking the ODS into outputing a single row with the true header names.
/* READ METADATA INFORMATION OF DATASET INTO ANOTHER DATASET */
PROC CONTENTS DATA = SASHELP.CLASS
OUT = WORK.CLSCONTENT;
RUN;
/* DEFINE HOW MANY COLUMNS EXIST IN THE DATASET */
PROC SQL NOPRINT;
SELECT MAX(VARNUM) INTO: TTLCOL FROM WORK.CLSCONTENT;
QUIT;
DATA _NULL_;
CALL SYMPUTX('TTLCOLS', 'C' || STRIP(&TTLCOL.));
RUN;
/* CREATES A NEW TABLE BREAKING OUT EACH COLUMN TO BE A VARIABLE (FOR OUTPUT)
RATHER THAN BEING A COLUMN NAME ITSELF */
DATA WORK.TRANSP;
SET CLSCONTENT(KEEP=NAME VARNUM);
RETAIN C1 - &TTLCOLS.;
ARRAY COL[*] $ C1 - &TTLCOLS.;
DO I=1 TO DIM(COL);
IF VARNUM = I THEN COL[I] = NAME;
END;
IF &TTLCOLS. = '' THEN DELETE;
DROP NAME VARNUM I;
RUN;
/* OUTPUT TABLE USING PROC REPORT WITH NOHEADER TO HIDE THE TRUE HEADER VALUES */
ODS EXCEL
FILE='/workspace/data/blnksheet.xlsx'
OPTIONS(SHEET_NAME='TEST-VAL' ABSOLUTE_COLUMN_WIDTH='30');
PROC REPORT DATA = WORK.TRANSP NOHEADER;
RUN;
QUIT;
ODS EXCEL CLOSE;
... View more