ODS Excel Issues and Proc Export

[BCNAV]

I am trying to update some older programming to use ODS. Outputting using ODS will create an unreadable excel file if the dataset is empty. We need to be able to output empty datasets to show that there is no result from an analysis. When using proc export, it will output to Excel fine, even if the dataset is empty. It will just have the variables in the dataset listed horizontally, as one would expect.

ODS Code:

%let date = %SYSFUNC(PUTN(%sysevalf(%SYSFUNC(TODAY())-1),DATE9.));ods excel file="\\ctxibs\IBSProd\IBS\Inputs\GAATS\NATGapCounter_&date..xlsx";
ods excel options(start_at="1,1" sheet_name="NATCOUNTER" sheet_interval="table" embedded_titles='yes');
	proc print data=EGTASK.NATCounter noobs; run;

Proc Export Code:

%let date = %SYSFUNC(PUTN(%sysevalf(%SYSFUNC(TODAY())-1),DATE9.));

/* Send to Excel */
proc export data=EGTASK.APPEND_TABLE_0038
	dbms=xlsx
	outfile="\\ctxibs\IBSProd\IBS\Inputs\GAATS\StrangeFlights_&date..xlsx"
	replace;
run;

Again ODS will create the Excel file if the dataset is empty, Excel will just say there are "unreadable" characters and bomb.

Proc Export is fine.

Is this just the case that ODS does not work in the case of empty datasets? If the dataset has no data, I need to still be able to write to Excel a file that has the variable names. Or should I just stick with proc export?

Cheers

[Reeza · Accepted solution]

PROC PRINT doesn't operate that way with an empty data set, so it's easier if you stick with PROC EXPORT. Otherwise you'll need some macro logic to output either the data set or the list of variables if it's empty.

The macro appendix (9.4) has some examples including one that shows how to accomplish this type of conditional print.

[SuzanneDorinski]

Chevell Parker mentioned this in his SAS Global Forum paper Secrets from a SAS Technical Support Guy: Combining the Power of the SAS® Output Delivery System wit....

Below is the macro from pages 4 and 5 of Chevell's paper. 

* create empty data set to use as example with macro ;

data temp;
  set sashelp.class(where=(name='Suzanne'));
run;

%macro test(libref=, dsn=);
	%let rc=%sysfunc(open(&libref..&dsn, i));
	%let nobs=%sysfunc(attrn(&rc, NOBS));
	%let close=%sysfunc(CLOSE(&rc));

	%if &nobs ne 0 and %sysfunc(Exist(&libref..&dsn)) %then
		%do;

			proc print data=&libref..&dsn;
			  title "%upcase(&libref..&dsn)";
			run;

		%end;
	%else
		%do;

			proc sql noprint;
				create table temp as select name from dictionary.columns where 
					libname=%upcase("&libref") and memname=%upcase("&dsn");
				run;
			quit;

			proc transpose data=temp out=temp1(drop=_label_ _name_);
				id name;
				var name;
			run;

			proc report noheader style(column)=header[just=center] nowd;
				title "No data for current report";
			run;

            title;
           
	%end;
%mend;

ODS EXCEL FILE="/folders/myfolders/SAS Communities/Excel output with empty dataset.xlsx"
  options(embedded_titles='yes'
          sheet_interval='proc');

%test(libref=work, dsn=temp) ;
%test(libref=sashelp, dsn=class) ;

proc datasets nolist;
  delete temp;
quit;

ODS EXCEL CLOSE;

[Tom]

You cannot PROC PRINT an empty dataset.  What is it supposed to do?

You could force your empty datasets to contain one record with all missing values and then print that instead.

You could even use a step like this to create two datasets out of your original file. So the EMPTY dataset will be empty if the REAL one is not empty. Otherwise it will have one observation with all missing values. Then just print both of them.

data real empty ;
  if _n_=1 and eof then output empty;
  set EGTASK.NATCounter end=eof;
  output real ; 
run;
proc print data=real noobs;
run;
proc print data=empty noobs;
run;