topic Re: Proc Report Sheet Name in ODS and Base Reporting

Proc Report Sheet Name

deleted_user — Wed, 18 Jul 2007 16:19:16 GMT

In ExcelXP I am trying to produce a single sheet per variable. I currently have the variable as a macro, but only get one tab as the last submitted macro variable. I have seen a few posts, but still cannot get a solution that works. below is the code if anyone has thought, please share.

%MACRO pool (p);
title;
ods listing close;
options missing=' ' center ;
ods tagsets.excelxp file="C:\Documents and Settings\name\Desktop\Append.xls"
style=xlrtf
options(sheet_interval='Proc' Sheet_Name="&p." center_horizontal='yes');
proc report data=stacked nowindows split='/'
style(summary)=Header;
where pool = "&p." and upb > 0;
column varf var1 total_upb upb six_plus f_upb c_upb ;
define varf / group ;
define var1 / 'Metric' group;
define total_upb / 'tot_upb' group;
define upb / '$ Balance' analysis sum style={tagattr='format:#,###'};
define six_plus / '$ Six' analysis sum;
define f_upb / 'F UPB' analysis sum;
define c_upb / 'C UPB' analysis sum;
rbreak after / summarize style={font_weight=bold background=lightgrey foreground=black};
compute before _page_ / style={font_weight=bold foreground=black font_size=6};
line "some title";
endcomp;
run;
ods tagsets.excelxp close;
ods listing;
%mend;
%pool(MLMI 2006-HE2);
%pool(MLMI 2006-HE4);

Re: Proc Report Sheet Name

deleted_user — Wed, 18 Jul 2007 18:40:01 GMT

Try splitting up the ods tagset.excelxp;

Put:
ods tagsets.excelxp file="C:\Documents and Settings\name\Desktop\Append.xls"
style=xlrtf
options(sheet_interval='Proc' center_horizontal='yes');
Outside the macro, before the call (%pool)

Put:
ods tagsets.excelxp options(Sheet_Name="&p.");
Inside the macro

Put:
ods tagsets.excelxp close;
ods listing;
Outside the macro, after the call

There may be other options available:
Search the forum for Excelxp and look for "by group processing" (which is what I spent the morning doing)

Re: Proc Report Sheet Name

Cynthia_sas — Wed, 18 Jul 2007 19:14:05 GMT

Hi...I think the issue is that you need to move your file creation option outside of the macro program:
[pre]
%macro pool(p=one);
....
** ONLY have your sheet_name option inside the macro definition;
ods tagsets.excelxp options(sheet_interval='table' sheet_name="&p");

** then proc report or other code;

%mend pool;

ods tagsets.excelxp file='OneWB.xls';
%pool(p=one);
%pool(p=two);
ods tagsets.excelxp close;
[/pre]

Or something very close to the above.

cynthia

Re: Proc Report Sheet Name

tbatliner — Wed, 13 Feb 2008 08:42:38 GMT

Hi,

thanks a lot, that really saved my day!

Great forum, best regards,

Thomas