ods tagsets.excelxp

[petlove]

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.

[ballardw]

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.

[ChrisNZ · Accepted solution]

 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;