topic Re: Blank Sheet/Workbook using ODS Excel in ODS and Base Reporting

Blank Sheet/Workbook using ODS Excel

WilliamD_ — Thu, 09 Jun 2016 17:04:33 GMT

Hi, I'm wondering if anyone else has attempted to use ODS EXCEL OPTION(Blank_Sheet='example_sheet') and been able to create a working work book?

My team and I have recently upgraded our server from an older UNIX machine running 9.2 to a Linux SAS Grid running 9.4. I have successfully migrated all of my ODS TAGSETS.EXCELXP code to output to the new ODS EXCEL outputs into xlsx which is far superior than the xml files I previously had to generate. However, I'm finding that while the SAS Support page shows the option to use BLANK_SHEET is there, all attempts to utilize this option have resulted in a file that excel thinks is corrupt.

Here's an example script:
ODS EXCEL
   FILE='/users/data/example.xlsx'
   STYLE = STATISTICAL
   OPTIONS(BLANK_SHEET = 'TEST');
QUIT;
ODS EXCEL CLOSE;

I've also tried inserting a PROC PRINT query to output a dataset with zero obs which produces the same corrupt file. Last, I actually attempted to make a multi-worksheet workbook with one table that has obs and the second without. This file does open however, the second sheet with zero obs does not exist in the file output (the workbook only shows the sheet with obs). I am using MS Office Excel 2010 to try and open these files.

I would greatly appreciate any feedback from the community on if others have experienced this and what work-arounds were used, will I be forced to continue to use the ODS TAGSETS.EXCELXP for my blank workbooks? Thanks!

Re: Blank Sheet/Workbook using ODS Excel

SuzanneDorinski — Sat, 11 Jun 2016 22:19:51 GMT

Which maintenance release of SAS 9.4 are you using?

This is the code that I tried in SAS 9.4 maintenance release 2:


ODS EXCEL FILE="H:\CenSAS stuff\ODS Excel examples\blank worksheet.xlsx"
  STYLE=Statistical
  options(blank_sheet='Test');

ODS EXCEL CLOSE;

ODS EXCEL FILE="H:\CenSAS stuff\ODS Excel examples\blank worksheet at beginning.xlsx"
  STYLE=Statistical
  options(blank_sheet='Test');

ODS EXCEL options(sheet_name='SASHELP.CLASS');

proc print data=sashelp.class;
run;

ODS EXCEL CLOSE;


ODS EXCEL FILE="H:\CenSAS stuff\ODS Excel examples\blank worksheet at end.xlsx"
  STYLE=Statistical
  options(sheet_name='SASHELP.CLASS');

proc print data=sashelp.class;
run;

ODS EXCEL options(blank_sheet='Test');

ODS EXCEL CLOSE;

The first file, just a blank worksheet, won't open. I get the message about the file being corrupt.

The second file, with a blank worksheet, followed by the sheet with the SASHELP.CLASS data set, opens. The first sheet is blank.

The third file, with the SASHELP.class data set on the first sheet, followed by a blank worksheet, opens. However, there's just the first worksheet in the file. The blank worksheet was not created.

I tried the same code in SAS 9.4 maintenance release 3, and all files were created as expected. The ODS Excel destination is experimental in SAS 9.4 maintenance releases 1 and 2.

Re: Blank Sheet/Workbook using ODS Excel

WilliamD_ — Mon, 13 Jun 2016 18:11:32 GMT

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;