BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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);
3 REPLIES 3
deleted_user
Not applicable
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)
Cynthia_sas
SAS Super FREQ
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
tbatliner
Calcite | Level 5
Hi,

thanks a lot, that really saved my day!

Great forum, best regards,

Thomas

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1816 views
  • 0 likes
  • 3 in conversation