When there are no observations in the file, gives err"Unable to read file"

[deleted_user]

Hi All..
I am creating an EXCEL file using the code below. File looks perfectly good when there is data. I am generating like 20 such files with multiple PROC PRINT statements. When there are no observations for a particular file , the file is getting generated but on opening the EXCEL file , it gives me "Unable to read file" error.
Can anyone help me out. I need to show blank sheet with title and header when there is no data.



PROC PRINT DATA=CHINA01 noobs SPLIT='*';
title1 'XXXXX';
title2 " &sysdate " " " " &systime " ;

VAR AAA/ style={just=c};
VAR BBB/ style={just=c};
VAR CCC/ style={just=c};
VAR DDD/ style={just=c};

LABEL AAA = 'Product*Family'
BBB = 'Unit*Code'
CCC = 'Division'
DDD = 'Territory' ;


ODS PATH(PREPEND) EXCELIB.TEMPLAT(READ);
ODS TAGSETS.EXCELXP FILE=XXXXX
STYLE=CUSTOM
RS=NONE
OPTIONS(SHEET_NAME = 'XXXXX'
FROZEN_HEADERS='4' AUTOFILTER='ALL'
EMBEDDED_TITLES='YES'
ORIENTATION = 'LANDSCAPE')
style=Style.Newfonts;

RUN;

ODS TAGSETS.EXCELXP CLOSE;

[David_SAS]

ODS is behaving as designed. The output file will be logically empty if the executed procedure(s) don't generate at least one output object (table or graph). Apparently Excel doesn't like empty files.

-- David Kelley, SAS

[JackHamilton]

As David says, you must have data or Excel isn't happy.

One workaround is to create an almost empty data set (1 row, all missing values) and print that if the original data set has no observations.

That's easy to do if your input data set is a real data set (not a view) and doesn't have deleted observations. See my ancient SUGI paper "How Many Observations Are In My Data Set?", <>.

You could try this, where &DATA is replaced by your data set name.

=====
data printme / view=printme;

if _n_ = 1 and no_more_data then
output;

set &DATA. end=no_more_data;

output;

run;

proc print data=printme noobs;
run;
=====

If &DATA has observations, they will be returned. If it has no observations, one observation with all missing values will be returned.

[OS2Rules]

I get this error all the time. It only seems to happen with the EXCELXP tagset.

From my experience, it has to do with a format or attribute of one of the output fields being incorrect and the out (which is XML) being incorrect. These are very difficult to find because the errors are logged in a file that is hidden (some form of MSO:Content name).

I usually try to strip off every format attribute of the output file and re-run to see if the file is now readable. If the error persists, your only option is to try to find the log file (you need to manually type the entire file name in) and see if it helps.