Suppress fields based on dataset in ods output

Q1983 — Mon, 14 Nov 2022 15:20:30 GMT

proc sql;
create table ic1 as
select 
		Ad_1,
		Pl_2
from ic
;quit;

proc sql;
create table cc1 as
select 
		H1_1,
		Pl_2
from pc
;quit;

%macro Report(report,sheet_nm);				
				
ODS TAGSETS.ExcelXP				
   options(sheet_interval='none'				
           absolute_column_width='8'				
           sheet_name=&sheet_nm.);
PROC REPORT DATA=&Report. headskip split='*' wrap nowd ;
columns Ad_1 Pl_2 H1_1 Pl_2;
	run;

%mend Report;				
				
%Report(ic1,"IC");	
%Report(cc1,"CC");

ODS TAGSETS.ExcelXP close;
  ods listing;

You cannot run this code however here is the question. In the columns area I list all 4 fields. However I want to just show the two fields that belong to the dataset as I have defined them in ic1 and cc1. The current code would list all 4 in both outputs. Is there a way to accomplish this without separating the individual proc report references. Even though I show only 4 fields my actual project has about 20 fields.

Re: Suppress fields based on dataset in ods output

PaigeMiller — Mon, 14 Nov 2022 17:39:29 GMT

@Q1983 wrote:
In the columns area I list all 4 fields. However I want to just show the two fields that belong to the dataset as I have defined them in ic1 and cc1. The current code would list all 4 in both outputs. Is there a way to accomplish this without separating the individual proc report references.

I'm not really sure what this means, but here is my guess as to what you mean. If this isn't what you want, explain in more detail.

%macro Report(report,sheet_nm,columns);				
				
ODS TAGSETS.ExcelXP				
   options(sheet_interval='none' absolute_column_width='8' sheet_name="&sheet_nm.");
PROC REPORT DATA=&Report. headskip split='*' wrap nowd ;
    columns &columns;
run;
ODS TAGSETS.ExcelXP close;
%mend Report;				
				
%Report(ic1,IC,Ad_1 Pl_2)
%Report(cc1,CC,H1_1 Pl_2)

Note your original code has ODS Tagsets.ExcelXP inside the macro, and ODS TAGSETS.EXCELXP CLOSE; outside the macro. This can't be right, but I don't know which way you want it. In any event, both ODS calls should both be inside the macro or both be outside the macro; and if they are outside the macro you need an additional ODS Tagsets inside the macro to set the sheet name.

By the way, why ODS TAGSETS Excel.XP when ODS EXCEL is avialable?

topic Re: Suppress fields based on dataset in ods output in ODS and Base Reporting

Suppress fields based on dataset in ods output

Re: Suppress fields based on dataset in ods output