How to export to excel a 0 observation data with a "No Data" note.

[sas_student1]

Hello,

I have been trying to google a solution for this but have not had too much success. Hoping folks here can help.

I have a dataset with 0 observation.

I want to export that dataset into an excel sheet with a note that says "No data available".

How do I do this?

I am using proc report that I export into an excel sheet. Sometimes the dataset will have data in it and the export will populate the excel sheet, other times the dataset may have no data in it, in that case instead of sas not creating an excel sheet I still would like to create the excel sheet but have a note that says "No data available".  Incase, folks suggest a call and execute, I already have an execute that I run before the proc report where I am adding a footnote. So if there is a suggestion to edit the below where if there is 0 observation then have a "No data note", otherwise run the below execute before the proc report, it will be helpful. Thank you!

data _null_;
set have ;
if index(clinic,'*') then do;
call execute 
('footnote h=11pt justify=left font=Calibri color=black bold  "* Multiple clinics are reported here";');
stop;
end;
run;

[japelin]

I don't know how it is output in the proc report because I don't have the code, but it looks like you can control the content of the footnote by checking the number of observations and branching the process with %if statement.

data _null_;
  set sashelp.vtable;
  where libname='WORK' and memname='HAVE';
  call symputx('nobs',nobs);
run;

data _null_;
%if &nobs=0 %then %do;
  call execute ('footnote h=11pt justify=left font=Calibri color=black bold  "No data available.";');
%end;
%else %do;
  set have ;
  if index(clinic,'*') then do;
  call execute ('footnote h=11pt justify=left font=Calibri color=black bold  "* Multiple clinics are reported here";');
  stop;
  end;
%end;
run;

[Kurt_Bremser]

@japelin this will no work:

data _null_;
%if &nobs=0 %then %do;
  call execute ('footnote h=11pt justify=left font=Calibri color=black bold  "No data available.";');
%end;
%else %do;
  set have ;
  if index(clinic,'*') then do;
  call execute ('footnote h=11pt justify=left font=Calibri color=black bold  "* Multiple clinics are reported here";');
  stop;
  end;
%end;
run;

as you cannot use %ELSE in open code.

But you can do this:

%if &nobs. = 0
%then %do;
footnote h=11pt justify=left font=Calibri color=black bold  "No data available.";
%end;
%if &nobs. ne 0
%then %do;
data _null_;
set have ;
if index(clinic,'*')
then do;
  call execute ('footnote h=11pt justify=left font=Calibri color=black bold  "* Multiple clinics are reported here";');
  stop;
end;
run;
%end;

One still has to decide what to do when no asterisk is found in the first observation, and how to activate the footnotes.

@sas_student1 Please post your complete REPORT code (including ODS statements for Excel), so we can see what you do when data is found; we can then make suggestions how to insert code for the "no data" situation.

[japelin]

@Kurt_Bremser Thank you for pointing this out.
I had lost track of the fact that it was open code.

[LeonidBatkhan]

Hi KurtBremser,

Actually, you can use %else in open code (I believe, starting SAS 9.4 M5):

%let A=0;
%if &a=1 %then
%do;
   %put THEN;
%end;
%else
%do;
   %put ELSE;
%end;

[Kurt_Bremser]

---

@LeonidBatkhan wrote:

Hi KurtBremser,

Actually, you can use %else in open code (I believe, starting SAS 9.4 M5):

%let A=0;
%if &a=1 %then
%do;
   %put THEN;
%end;
%else
%do;
   %put ELSE;
%end;

---

But there must have been a time when %IF %THEN was possible, but not the %ELSE?

When exactly became %IF available in open code? The macro language reference in the documentation somehow does not show this, and I also didi not find a reference in the "What's new" section.

[LeonidBatkhan]

@Kurt_Bremser,

This became available with SAS 9.4 M5 (maintenance release 5) sometime in spring 2018, both %IF-%THEN-%DO and %ELSE-%DO.

See this blog post by @ChrisHemedinger

Using %IF-%THEN-%ELSE in SAS programs

[Tom]

There is no need for the extra step to count the observations or the macro code. Use the NOBS option of the SET statement.

So your example reduces to :

data _null_;
  if nobs=0 then call execute('footnote h=11pt justify=left font=Calibri color=black bold "No data available.";');
  set have nobs=nobs;
  if index(clinic,'*') then do;
    call execute('footnote h=11pt justify=left font=Calibri color=black bold "* Multiple clinics are reported here";');
    stop;
  end;
run;

[sas_student1]

@Tom 

Tried the below code it didn't work 😞

I did post the full code that I was using. 

Thanks!

[Tom]

Did you try the code using the NOBS= option to allow you to check if the source dataset is empty?

Make sure to test for zero observations BEFORE the SET statement.  Because if wait until after the SET statement then it will be too late and the test will never execute.

Most SAS data steps end when they read past the input in a SET or INPUT statement.  Not at the last line of code in the data step.

[sas_student1]

@Tom 

Below is my full code, where I added the nobs=0 suggestion. 

I did before the set statement as per the code.

Not sure what I am doing incorrectly.

ods excel file ='C:\Report.xlsx' ;
ods escapechar ='^';
ODS excel options(Embedded_Titles ='yes' embedded_footnotes='yes' sheet_name='New Patient');

title1 j=l h=11pt color=black font=Calibri bold 
"^{style[fontweight=bold]
Summary of ^{style[fontstyle=italic] New} Patient by County and Clinic}";

title2 j=l h=11pt color=black font=Calibri  
"^{style[fontweight=bold]Date Complete: ^{style[color=blue fontstyle=italic]%sysfunc(date(),worddate18.)}}";

data _null_;
if nobs=0 then call execute('footnote h=11pt justify=left font=Calibri color=black bold "No data available.";');
set zero_obs nobs=nobs;
if index(clinic,'*') then do;
call execute 
('footnote h=11pt justify=left font=Calibri color=black bold  "* Multiple clinics are reported here";');
stop;
end;
run;

 proc report data=zero_obs 
 style(header)=[background=darkmagenta color=white font_face='Calibri' fontsize=11pt fontweight=bold]
 style(column)={font_face='Calibri' fontsize=11pt};
  column County Clinic new_pat ;
  define County / group;
  define Clinic / group;
  define new_pat / sum;

rbreak after / summarize style=Header;

  Compute after;
  Clinic ='GRAND TOTAL';
  endcomp;

 run;

ODS excel options (Embedded_Titles ='yes' embedded_footnotes='yes' sheet_name='Existing patients');
title1 j=left color=black font='Calibri' bold
"Summary of Existing pateints by clinic";
title2 j=l h=11pt color=black font=Calibri  
"^{style[fontweight=bold]Date Complete: ^{style[color=blue fontstyle=italic]%sysfunc(date(),worddate18.)}}";

data _null_;
set existing_pt ;
if index(program_name,'*') then do;
call execute 
('footnote h=11pt justify=left font=Calibri color=black bold  "* Multiple programs are reported here";');
stop;
end;
run;

proc report data=existing_pt 
 style(header)=[background=darkmagenta color=white font_face='Calibri' fontsize=11pt fontweight=bold]
 style(column)={font_face='Calibri' fontsize=11pt};
  column clinic existing_pt;
  define clinic / group;
  define existing_pt / sum;

  rbreak after / summarize style=Header;

  Compute after;
  clinic ='GRAND TOTAL';
  endcomp;
 run;

ods excel close;
title1 "";

[Tom · Accepted solution]

Since you are using PROC REPORT you cannot depend on it generating ANY output.

Try this test:

proc report data=sashelp.class(obs=0);
run;

If you used PROC EXPORT to write the sheet then it would run even if the dataset is empty.

So in that case you need to use something other than the FOOTNOTE to tell the users there is no data.

You could use FILE PRINT.

data class1 class2;
  set sashelp.class(obs=5);
  output class2 ;
run;

ods excel file='c:\downloads\empty.xlsx';
ods excel options (sheet_name='CLASS1');

data _null_;
  if nobs=0 then do;
     file print titles;
     put // 'There is no data';
  end;
  set class1 nobs=nobs;
run;

proc print data=sashelp.class1;
run;

ods excel options (sheet_name='CLASS2');

data _null_;
  if nobs=0 then do;
     file print titles;
     put // 'There is no data';
  end;
  set class2 nobs=nobs;
run;

proc print data=class2;
run;

ods excel close;

Or you could add an empty observation to the dataset you are planning to pass to PROC REPORT. (Remember to remove the STOP statement and add an explicit OUTPUT to copy the data when it is present.

data zero_obs_report;
  if nobs=0 then do;
     call execute('footnote h=11pt justify=left font=Calibri color=black bold "No data available.";');
     output;
  end;
  set zero_obs nobs=nobs;
  if index(clinic,'*') then do;
    call execute 
      ('footnote h=11pt justify=left font=Calibri color=black bold  "* Multiple clinics are reported here";')
    ;
  end;
  output;
run;

proc report data=zero_obs_report  ....

[sas_student1]

@Tom  YOU ARE A GENIUS!!!!!!

All your suggestions were great but the last option worked!!!

You ROCK!! YOU ALL ROCK!!!!

THANK YOU!

[sas_student1]

@Tom @japelin 

below is the full code that I have that works so far, except the issue I have now.

If the dataset has  0 observations (see "dataset = datanew" in the code below) then it will not export any excel sheet. I should have noted that I am running two serpate proc reports and exporting the restuls in one excel with two sheets. So one dataset will always have some data (see data = data_cum) and it will export to the excel sheet, but the other dataset (datanew) sometimes may have data to export and other times will have 0 observations, at which point there will be no excel sheet produced at all, this dataset is created conditional of the day of the reporting. Suggestions? I will try @japelin suggestions below i wonder if that will work.

ods excel file ='C:\report.xlsx' ;
ods escapechar ='^';
ODS excel options(Embedded_Titles ='yes' embedded_footnotes='yes' sheet_name='New Patients');

title1 j=l h=11pt color=black font=Calibri bold 
"^{style[fontweight=bold]
Summary of ^{style[fontstyle=italic] New} Patients County and Clinic}";

title2 j=l h=11pt color=black font=Calibri  
"^{style[fontweight=bold]Date Complete: ^{style[color=blue fontstyle=italic]%sysfunc(date(),worddate18.)}}";

data _null_;
set x.datanew ;
if index(clinic,'*') then do;
call execute 
('footnote h=11pt justify=left font=Calibri color=black bold  "* Multiple clinics are reported here";');
stop;
end;
run;

 proc report data=x.datanew
 style(header)=[background=darkmagenta color=white font_face='Calibri' fontsize=11pt fontweight=bold]
 style(column)={font_face='Calibri' fontsize=11pt};
  column County clinic New_patients;
  define County / group;
  define clinic  / group;
  define New_patients/ sum;

  rbreak after / summarize style=Header;

  Compute after;
  clinic   ='GRAND TOTAL';
  endcomp;

 run;

ODS excel options (Embedded_Titles ='yes' embedded_footnotes='yes' sheet_name='Existing patients');
title1 j=left color=black font='Calibri' bold
"Summary of Exisiting patients by clinic";
title2 j=l h=11pt color=black font=Calibri  
"^{style[fontweight=bold]Date Complete: ^{style[color=blue fontstyle=italic]%sysfunc(date(),worddate18.)}}";

data _null_;
set x.data_cum ;
if index(clinic,'*') then do;
call execute 
('footnote h=11pt justify=left font=Calibri color=black bold  "* Multiple clinic are reported here";');
stop;
end;
run;

proc report data=x.data_cum
 style(header)=[background=darkmagenta color=white font_face='Calibri' fontsize=11pt fontweight=bold]
 style(column)={font_face='Calibri' fontsize=11pt};
  column clinic cum_pt ;
  define clinic / group;
  define cum_pt / sum;

  rbreak after / summarize style=Header;

  Compute after;
  clinic  ='GRAND TOTAL';
  endcomp;
 run;
ods excel close;

[andreas_lds]

Try something like

/* fake data */
data work.have;
   if 0 then set sashelp.class(rename=(name=clinic));
   stop;
run;

%global hasdata;

data _null_;
   if _nobs then do;      
      call symputx('hasdata', 'yes');
   end;
   else do;
      call symputx('hasdata', 'no');
   end;

   set have nobs=_nobs;
run;

%put &=hasdata;

ods excel file="&Benutzer\temp\empty.xlsx";

/* requires sas 9.4m5 or later */
%if &hasdata. = no %then %do;
   ods text="No data available";
%end;

proc report data=have;
   columns Age Weight Height;
   define Age / group;
   define Weight / median;
   define Height / max;
run;

ods excel close;