Multiple Sheet Output ODS Excel with Proc SQL

[TylerP8813]

Currently, I am generating a report on a singular worksheet using ODS Excel Output with Proc Sql steps that all query the same flat file and create about 15 single row "tables" Code example below. 

I'm trying to convert it to a macro that will run all of the same proc sql steps but create unique tabs for each value as assigned from a separate table. When it runs, it either puts all the resulting tables on one tab (if sheet_interval="none") or one proc result on each tab if I specify another option. I want the macro to run all the proc sql steps per the passed value and then create a new worksheet tab with the new passed value.

Example Code:

proc sql;
create table PassedValue as
select distinct PassedValue
from All_Clients
order by PassedValue;
quit;

ods excel file="file location/File I want.xlsx";

%macro PM_Measure(PassedValue = );
ods excel options(Sheet_Interval='none' Sheet_Name="&PassedValue." embedded_titles="YES" embedded_footnotes="YES");

title 1 "Title 1";

title3 "Title 3";

footnote2 "footnote 2";

Proc Sql;

select count(Value1)

from All_Clients

where Condition1="YES" and Condition2="YES" and PassedValue="&PassedValue.";

quit;

....... (14 more versions of similar step)

%mend;

data _null_;
set PassedValue;
str= catt('%PM_Measure(PassedValue=', PassedValue, ');');
call execute(str);run;
ods excel close;

[SASJedi · Accepted solution]

I'd recommend using SHEET_INTERVAL='PROC' and generating all of the report iterations using a single PROC SQL step. Something like this:

ods excel file="file location/File I want.xlsx";
%macro PM_Measure(PassedValue=);
	ods excel options(Sheet_Interval='PROC' Sheet_Name="&PassedValue." 
		embedded_titles="YES" embedded_footnotes="YES");
	proc sql;
		title1 "Title 1";
		title3 "Title 3";
		footnote2 "footnote 2";
		select count(Value1) 
			from All_Clients 
			where Condition1="YES" and Condition2="YES" 
				   and PassedValue="&PassedValue."
		;
	/* 14 more versions of similar step */
	quit;
%mend;
data _null_;
	set PassedValue;
	str=catt('%PM_Measure(PassedValue=', PassedValue, ');');
	call execute(str);
run;
ods excel close;

That should produce a single tab in the Excel workbook for each iteration of the DATA step.

May the SAS be with you!
Mark

[TylerP8813]

Thanks! Combining it all into one Proc step did the trick! 

You wouldn't happen to know an easy way to add in a quick comparison percentage would you? Every first select statement is a numerator and every second select step is a denominator. 

[SASJedi]

Easiest way I can think of is to use a macro variable. For example:

proc sql;
title "Audi";
select count(*) as Count
	into: count
	from sashelp.cars
	where make='Audi'
;
select &count/count(*) format=percent6.1 as Percent
	from sashelp.cars
;
title "Ford";
select count(*) as Count
	into: count
	from sashelp.cars
	where make='Ford'
;
select &count/count(*) format=percent6.1 as Percent
	from sashelp.cars
;
quit;

Yields:

Audi

Count
19

Audi

Percent
4.4%

Ford

Count
23

Ford

Percent
5.4%

[TylerP8813]

Ok, so I tried that approach and a few unexpected things happened in my output.

I put the Numerator and the Denominator each into their own Macro. 

For one, I got about the same number of rows for the resultant table that printed out as there are records in the base table (about 7000). Also, the values for each of the two text Macros (Numerator and Denominator) were set to the values from the initial iteration of the report macro and were not overwritten in subsequent iterations of the report macro.

Thanks in advance for any further help.

[TylerP8813]

Nevermind. I was able to use distinct in the select statement to remove the extra rows and a %SYMDEL step at the end of the macro to clear the macro values.

[Reeza]

NOW + NONE options?

proc sql;
create table PassedValue as
select distinct PassedValue
from All_Clients
order by PassedValue;
quit;

 

ods excel file="file location/File I want.xlsx" options(sheet_interval='none');

%macro PM_Measure(PassedValue = );
ods excel options(Sheet_Interval='now' Sheet_Name="&PassedValue." embedded_titles="YES" embedded_footnotes="YES");

title 1 "Title 1";

title3 "Title 3";

footnote2 "footnote 2";

Proc Sql;

select count(Value1)

from All_Clients

where Condition1="YES" and Condition2="YES" and PassedValue="&PassedValue.";

quit;

....... (14 more versions of similar step)

%mend;

 

data _null_;
set PassedValue;
str= catt('%PM_Measure(PassedValue=', PassedValue, ');');
call execute(str);run;
ods excel close;

Spoiler

---

@TylerP8813 wrote:

Currently, I am generating a report on a singular worksheet using ODS Excel Output with Proc Sql steps that all query the same flat file and create about 15 single row "tables" Code example below. 

I'm trying to convert it to a macro that will run all of the same proc sql steps but create unique tabs for each value as assigned from a separate table. When it runs, it either puts all the resulting tables on one tab (if sheet_interval="none") or one proc result on each tab if I specify another option. I want the macro to run all the proc sql steps per the passed value and then create a new worksheet tab with the new passed value.

Example Code:

proc sql;
create table PassedValue as
select distinct PassedValue
from All_Clients
order by PassedValue;
quit;

ods excel file="file location/File I want.xlsx";

%macro PM_Measure(PassedValue = );
ods excel options(Sheet_Interval='none' Sheet_Name="&PassedValue." embedded_titles="YES" embedded_footnotes="YES");

title 1 "Title 1";

title3 "Title 3";

footnote2 "footnote 2";

Proc Sql;

select count(Value1)

from All_Clients

where Condition1="YES" and Condition2="YES" and PassedValue="&PassedValue.";

quit;

....... (14 more versions of similar step)

%mend;

data _null_;
set PassedValue;
str= catt('%PM_Measure(PassedValue=', PassedValue, ');');
call execute(str);run;
ods excel close;

---