Hi,
I have code below to create one excel file with different sheets for different datasets.
In below code, %dqcreadin macro creates &p21id and &title macro variables. It also create DQCOUT dataset in work library which I am deleting by suing proc datasets step.
In addition to expected datasets, it also creates additional sheets:
for each dataset, sheet for values of &p21id and &title
%macro excel;
ods listing close;
ods tagsets.excelxp file='&path/test.xls' ;
%let datasets=AE#DM;
%do i = 1 %to %eval(%sysfunc(count(&datasets,#))+1);
%let dsn=%scan(&datasets,&i,#);
%dqcreadin(id=&dsn);
proc datasets library=work;
delete dqcout;
run;
ods tagsets.excelxp options(sheet_name="&dsn" embedded_titles='yes' embedded_footnotes='yes' );
title j=l "&p21id &title";
proc print data=&dsn;
run;
footnote1 j=l font=arial height=10pt "&lineland";
footnote2 j=l font=arial height=10pt "Source: &sysin -- &p21id -- &_uid.V&sysver &sysdate9.:&systime. Source data:SDTM. Data Transfer: &datadate";
%end;
ods tagsets.excelxp close;
ods listing;
%mend;
Can anyone help me to find out how we can control ODS tagsets.excelxp to create sheets for only "proc print" of &dsn?
Thank you.
create sheets for only "proc print"
What else do you get in Excel?
Do you just need to add this option?
proc datasets library=work noprint;
How does your macro %dqcreadin(id=&dsn) create that data set? Are you wanting to suppress output from that macro appearing in your output? With out knowing what might be creating output it is hard to make suggestions to suppress the output other than moving creating data set creation outside of the Ods tagsets.excelxp / ods tagsets.excelxp close; sandwich. I am not sure exactly why, at this time, why you are worried about the data set dqcout as you do not show using it in this macro other than the delete.
Proc Datasets has an option NOPRINT so none of the output it might create would end up in the document.
The tagsets.excelxp option sheet_interval generally controls when a new sheet gets created. You may want options (sheet_interval='PROC') on the main ODS tagsets.excelxp statement.
create sheets for only "proc print"
What else do you get in Excel?
Do you just need to add this option?
proc datasets library=work noprint;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.