The SAS Output Delivery System and reporting techniques

Create Empty Sheet in a Workbook Using Excel Tagset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Create Empty Sheet in a Workbook Using Excel Tagset

Hi,

I am creating a data summary pulling in multiple tables. Using Proc Report and the Excel Tagset, each tableappears on a separate sheet/tab in an Excel file. In the situation where the source table does not contain any observations, I would like to create a blank sheet. Any suggestions on how to produce a blank sheet stating only "NO DATA AVAILABLE" ?

Thanks


Accepted Solutions
Solution
‎12-04-2011 01:14 PM
SAS Super FREQ
Posts: 8,740

Re: Create Empty Sheet in a Workbook Using Excel Tagset

Hi:

  My initial take on it is that you'd want to introduce some SAS Macro logic into your program and if your table had observations, you would use your regular PROC REPORT code. If your data does not have observations, then you would create an "error message" dataset and print that to the sheet, instead. I used the example below in a previous forum posting.

cynthia

%macro prt(libname=, fname=, whcls= 1);
           
  proc sort data=&libname..&fname out=subset;
    by name age;
    where &whcls;
  run;
              
  %LET dsid=%SYSFUNC(OPEN(work.subset));
  %LET nobs=%SYSFUNC(ATTRN(&dsid.,NOBS));
  %LET rc=%SYSFUNC(CLOSE(&dsid.));

                    

  %if &nobs = 0 %then %do;
    data errmsg;
      errmsg = "Your where clause did not retrieve any records.";
      output;
      errmsg = "&whcls";
      output;
    run;
               
    proc print data=errmsg;
    run;
                  
  %end;
  %else %if &nobs ne 0 %then %do;
    proc print data=work.subset;
      title "Condition Met: &whcls";
    run;
  %end;
    
  title;
  footnote;
  proc datasets lib=work nods;
    delete subset errmsg;
  run;
  quit;
%mend prt;   
                               
ods tagsets.excelxp file='c:\temp\multprt.xml'
    style=sasweb options(sheet_name='One');
    %prt(libname=sashelp, fname=class, whcls = age eq 12);
    run;
      
ods tagsets.excelxp options(sheet_name='Two');
    %prt(libname=sashelp, fname=class, whcls = age eq 14);
    run;

       

ods tagsets.excelxp options(sheet_name='Three');
    %prt(libname=sashelp, fname=class, whcls = age eq 18);
    run;

                
ods tagsets.excelxp close;

View solution in original post


All Replies
Solution
‎12-04-2011 01:14 PM
SAS Super FREQ
Posts: 8,740

Re: Create Empty Sheet in a Workbook Using Excel Tagset

Hi:

  My initial take on it is that you'd want to introduce some SAS Macro logic into your program and if your table had observations, you would use your regular PROC REPORT code. If your data does not have observations, then you would create an "error message" dataset and print that to the sheet, instead. I used the example below in a previous forum posting.

cynthia

%macro prt(libname=, fname=, whcls= 1);
           
  proc sort data=&libname..&fname out=subset;
    by name age;
    where &whcls;
  run;
              
  %LET dsid=%SYSFUNC(OPEN(work.subset));
  %LET nobs=%SYSFUNC(ATTRN(&dsid.,NOBS));
  %LET rc=%SYSFUNC(CLOSE(&dsid.));

                    

  %if &nobs = 0 %then %do;
    data errmsg;
      errmsg = "Your where clause did not retrieve any records.";
      output;
      errmsg = "&whcls";
      output;
    run;
               
    proc print data=errmsg;
    run;
                  
  %end;
  %else %if &nobs ne 0 %then %do;
    proc print data=work.subset;
      title "Condition Met: &whcls";
    run;
  %end;
    
  title;
  footnote;
  proc datasets lib=work nods;
    delete subset errmsg;
  run;
  quit;
%mend prt;   
                               
ods tagsets.excelxp file='c:\temp\multprt.xml'
    style=sasweb options(sheet_name='One');
    %prt(libname=sashelp, fname=class, whcls = age eq 12);
    run;
      
ods tagsets.excelxp options(sheet_name='Two');
    %prt(libname=sashelp, fname=class, whcls = age eq 14);
    run;

       

ods tagsets.excelxp options(sheet_name='Three');
    %prt(libname=sashelp, fname=class, whcls = age eq 18);
    run;

                
ods tagsets.excelxp close;

Occasional Contributor
Posts: 17

Create Empty Sheet in a Workbook Using Excel Tagset

Thank you for this response - I knew I couldn't be the first person having this issue, but I couldn't locate the answer on a search. This is great!! I'm not too familiar with macros, but I guess I'll learn.

Valued Guide
Posts: 2,174

Create Empty Sheet in a Workbook Using Excel Tagset

I tried to solve the same problem in a different way

If a table is empty (having no rows), add an empty row

Then proc print will print an empty row (meaningful to me!)

%macro IFz1(data=&syslast) /des= 'if Zero make 1 obs' ;

data &data ;

   if nobs then stop ;

   output ;

   modify &data nobs=nobs ;

   stop ;

run ;

%mend  IFz1 ;

/* demo

option mlogic symbolgen ;

data class; stop; set sashelp.class; run;

%ifz1 ;

    */

The macro assumes it is able to update the table.

I would use it before any proc print where I want an empty row if the data set is empty.

Contributor sks
Contributor
Posts: 42

Create Empty Sheet in a Workbook Using Excel Tagset

Peter,

    I use a logic similar to yours, but if there are no observations, I have a table with 1 column called msg and the contents of the column says "No Data Found". I use Proc report to output the table with the 1 column. I have seen it is more benefitial to let the user know that no data was found rather than open an empty excel file.

Shri

Occasional Contributor
Posts: 17

Create Empty Sheet in a Workbook Using Excel Tagset

Thank you for your response - you are right - it is definitely helpful to have an explanatory message.

Occasional Contributor
Posts: 17

Create Empty Sheet in a Workbook Using Excel Tagset

Thanks for this example. I'm learning about macros at the same time, so this is great. I could also use this to add an "END OF FILE" line to the excel data.

Occasional Contributor
Posts: 17

Create Empty Sheet in a Workbook Using Excel Tagset

Hi All,

I appreciate everybody's quick responses. I just tried them out, and was able to achieve the result that I need.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1728 views
  • 9 likes
  • 4 in conversation