Insert text into Excel when there is no data to report

[Jeff_DOC]

Hello!

I hope someone can point me in the right direction and I'm sure they can.

I'm attempting to generate an Excel file with data if data is available. If not, I simply want the same file name with no data but with text entered in cell A2 (or a blank Excel sheet with text in A1). The text should simply say "No records were found." I've figured out how to count the observations so I know if I'm at zero or non-zero and how to test for that. The Excel file generates correctly if there is data but the "No records were found." doesn't generate. I just get an error message that says the Excel can't open the file. I'm assuming this is because there's no data in it but I'm not sure of that.

I've inherited this code and now have to figure out how to make it work. I'd sure appreciate any help.

Thank you.

	/*The first section is if there is data to report.*/
%macro which_report;

%if "&count." > 0 %then %do;

ods listing close;
options missing = ' ';
ods Excel file = "<<PATH>>"
options (sheet_interval = 'none'
				sheet_interval = 'none'
				sheet_name = 'DATA');

proc report data = HAVE nowd
	style(REPORT) = {cellpadding = 5 background = #D0C7A8 font_size = 9pt}
	style(COLUMN) = {font_face = Arial background = white font_size = 9pt}
	style(HEADER) = {font_face = Arial foreground = black background = #EBE8DA font_size = 9pt};

	column VARIOUS COLUMNS;


run;

ods Excel close;
ods listing;

%end;
%else %do;

	/*This section is for when there is no data to report.*/

	data _null_;
	file "<<PATH>>";
	put "No records were found.";
	run;

ods Excel close;
ods listing;

%end;

%mend;
%which_report;

[ballardw]

My take:

%macro which_report;

ods listing close;
options missing = ' ';
ods Excel file = "<<PATH>>"
options (sheet_interval = 'none'
				sheet_interval = 'none'
				sheet_name = 'DATA');


%if "&count." > 0 %then %do;

proc report data = HAVE nowd
	style(REPORT) = {cellpadding = 5 background = #D0C7A8 font_size = 9pt}
	style(COLUMN) = {font_face = Arial background = white font_size = 9pt}
	style(HEADER) = {font_face = Arial foreground = black background = #EBE8DA font_size = 9pt};

	column VARIOUS COLUMNS;


run;

%end;
%else %do;

	/*This section is for when there is no data to report.*/
Proc odstext;
p "No records were found";
;
run;
%end;
ods Excel close;
ods listing;

%mend;
%which_report;

You can use a data step to write to ODS destinations but I think Proc ODSTEXT may be more flexible than File print ods.

You could add style bits at the end of the P statement in Odstext to change font, size or other highlight measures.

[Jeff_DOC]

Thanks for the assist. I really appreciate it. However, this solution prints the text to the results window. I need it to be printed to the Excel sheet itself.

[ballardw]

SHOW the CODE that you actually ran. From the log with the macro compilation included.

[SASKiwi]

You are missing an ODS EXCEL statement just before your DATA step outputing the message.

[Tom · Accepted solution]

You seem to have the %IF/%THEN blocks in the wrong place.  

The program flow should be:  OPEN ODS DESTINATION, PRINT OUTPUT, CLOSE ODS DESTINATION.

Note that if the input dataset, HAVE in your code, has zero observations then there is no need to actually skip the PROC REPORT step as that won't do anything with an empty dataset.  And if you use a DATA step to write the text you can make it only write when there is no data.  So you really don't need macro logic at all.  So do you even need to define a macro?

ods listing close;
options missing = ' ';
ods Excel file = "<<PATH>>"
  options 
   (sheet_interval = 'none'
  	sheet_name = 'DATA'
   )
;

proc report data = HAVE nowd
  style(REPORT) = {cellpadding = 5 background = #D0C7A8 font_size = 9pt}
  style(COLUMN) = {font_face = Arial background = white font_size = 9pt}
  style(HEADER) = {font_face = Arial foreground = black background = #EBE8DA font_size = 9pt}
;
  column VARIOUS COLUMNS;
run;

data _null_;
  if nobs then stop;
  file print;
  put "No records were found.";
  stop;
  set have nobs=nobs;
run;

ods Excel close;
ods listing;

[Jeff_DOC]

Hi Tom.

Thank you for that. Much simpler and more efficient. One small issue is that the text that is placed is wrapped and the column width forces the text to not display correctly. Do you know of a way to control for this?

[Tom]

You could just make a dataset and print that.

data empty;
  message= "No records were found.";
  if nobs =0 then output;
  stop;
  set have(drop=_all_) nobs=nobs;
run;
proc print data=empty;
run;