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

Hey Peter, while that does work better for the excelxp tagset, my customers prefer the .xls format. ... View more

I thought I'd follow up with an update to this problem. The PC Files Server running on the windows machine defaults to no user specific login credentials... The read/write permissions are inherited by the login credentials of the service, not the user logged into the machine itself.  Essentially, you need to either setup a new user profile with the appropriate drive read/write access for all directories you intend to write to or you input your own login credentials and manually run the service daily (delayed start doesn't always work depending on network setup). Because of the security risks involved in this, unfortunately I've had to abandon this method. I've since started using ODS tagset.excelxp and then declaring my var / style = {tagattr='FORMAT:TEXT'}; to prevent truncation of numerical data upon opening in excel.  This method will allow Windows 7 Office 2010 to open the .xls extension files but not .xlsx extension files. ... View more

Hi Linus, I'm using a Xterm to remote into the UNIX server where I write and process my SAS programs.  All of my teams original code is written using the DBMS = XLS which works for people still using Windows XP, but everyone who's migrated to Windows 7 cannot.  This is a known issue due to the fact that the XLS output is designed for Microsoft Office 2003 and it commonly fails when trying to open in WIN 7 64bit running Office 32 bit. The only work around to make an xls that is usable in WIN 7 is to either use DBMS tab - but I can't expect my customers to know how to import the data into a table properly and I can't cheat my naming the extension .xls.  The data I work with has numerical values that often times exceed 15 digits, so most of the exports that fail to include metadata are unusable to me.  As far as email - the data is usually emailed monthly as the programs finish collecting their data. Unfortunately, you cannot run the PC Files server on UNIX as it's designed to run on Windows as a way to make a Microsoft compliant file. I work for a large Corp... Upgrading to Base SAS 9.3 for UNIX isn't an option: the server needs to be upgraded first and there is already development to upgrade to 9.4/9.5 in 2015. ... View more