topic Re: How to export to excel a 0 observation data with a "No Data" note. in SAS Programming

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

sas_student1 — Wed, 28 Apr 2021 05:50:32 GMT

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;

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

japelin — Wed, 28 Apr 2021 06:23:10 GMT

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;

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

andreas_lds — Wed, 28 Apr 2021 06:52:18 GMT

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;

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

Kurt_Bremser — Wed, 28 Apr 2021 07:07:26 GMT

@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.

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

japelin — Wed, 28 Apr 2021 07:26:17 GMT

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

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

LeonidBatkhan — Wed, 28 Apr 2021 14:50:37 GMT

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;

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

Kurt_Bremser — Wed, 28 Apr 2021 15:38:39 GMT

@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.

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

Tom — Wed, 28 Apr 2021 15:43:49 GMT

Does it not create the sheet at all?

Or does it create the sheet with column headers but no rows of data?

If the later is there really any need to add something additional?

If the former then you probably just need to run a separate step to create the sheet you want to appear when there is no data.

Please show the code you are currently using to write the sheet as the way to adjust the code depends on what code you are currently using..

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

LeonidBatkhan — Wed, 28 Apr 2021 15:50:41 GMT

@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

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

Tom — Wed, 28 Apr 2021 16:05:52 GMT

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;

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

sas_student1 — Wed, 28 Apr 2021 16:32:59 GMT

@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;

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

sas_student1 — Wed, 28 Apr 2021 23:04:30 GMT

@Tom

Tried the below code it didn't work 😞

I did post the full code that I was using.

Thanks!

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

Tom — Wed, 28 Apr 2021 23:20:23 GMT

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.

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

sas_student1 — Wed, 28 Apr 2021 23:10:56 GMT

Thank you @andreas_lds for the suggestion, unfortunately it didn't work 😞

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

sas_student1 — Wed, 28 Apr 2021 23:20:34 GMT

@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 "";

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

Tom — Wed, 28 Apr 2021 23:38:00 GMT

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  ....

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

sas_student1 — Thu, 29 Apr 2021 00:42:52 GMT

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

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

You ROCK!! YOU ALL ROCK!!!!

THANK YOU!