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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

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;

LSchafer
Calcite | Level 5

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.

Peter_C
Rhodochrosite | Level 12

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.

sks
Fluorite | Level 6 sks
Fluorite | Level 6

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

LSchafer
Calcite | Level 5

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

LSchafer
Calcite | Level 5

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.

LSchafer
Calcite | Level 5

Hi All,

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 7 replies
  • 4476 views
  • 10 likes
  • 4 in conversation