topic Re: Multiple Sheet Output ODS Excel with Proc SQL in SAS Programming

Multiple Sheet Output ODS Excel with Proc SQL

TylerP8813 — Wed, 23 Nov 2022 20:58:14 GMT

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;

Re: Multiple Sheet Output ODS Excel with Proc SQL

SASJedi — Thu, 24 Nov 2022 16:24:26 GMT

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

Re: Multiple Sheet Output ODS Excel with Proc SQL

Reeza — Thu, 24 Nov 2022 18:08:45 GMT

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;

@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;

Re: Multiple Sheet Output ODS Excel with Proc SQL

TylerP8813 — Mon, 28 Nov 2022 20:52:54 GMT

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.

Re: Multiple Sheet Output ODS Excel with Proc SQL

SASJedi — Mon, 28 Nov 2022 21:38:39 GMT

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%

Re: Multiple Sheet Output ODS Excel with Proc SQL

TylerP8813 — Mon, 28 Nov 2022 22:22:40 GMT

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.

Re: Multiple Sheet Output ODS Excel with Proc SQL

TylerP8813 — Tue, 29 Nov 2022 21:08:28 GMT

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.