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
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;
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;
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.
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.
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
Thank you for your response - you are right - it is definitely helpful to have an explanatory message.
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.
Hi All,
I appreciate everybody's quick responses. I just tried them out, and was able to achieve the result that I need.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.