BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BCNAV
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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.

View solution in original post

3 REPLIES 3
Reeza
Super User

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
Lapis Lazuli | Level 10

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
Super User Tom
Super User

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4616 views
  • 1 like
  • 4 in conversation