Hello
Following code below is creating one excel file with one sheet and 4 tables .
I want to have 2 sheets and each sheet will have 2 tables.
Can anyone fix the code and show how to do it?
I just know to add sheet_interval="none" but when i add it I get 4 sheets with 1 table in each sheet
ods _all_ close;
ods tagsets.ExcelXP file="/usr/local/SAS/RON/UserDir/Tbl8.XML"
style=Printer
options(sheet_name="Sheet1Ron"
embedded_titles='yes'
embedded_footnotes='yes') ;
%macro print_age(age);
Proc print data = sashelp.class;
Where age = &age. ;
title "age : &age." ;
footnote1 '*Height in CM';
footnote2 '*Weight in KG';
Run;
PROC SQL;
create table NoLak as
select count(*) as No_Lak
from sashelp.class
where age = &age.
;
QUIT;
proc print data=NoLak noobs;run;
%mend;
%print_age(13);
%print_age(16);
ods tagsets.ExcelXP close;
If you want multiple tables in an Excel file ... use file= on the first sheet, plus the sheet_name= option; on the second sheet, DO NOT use file= but do use sheet_name=
As with all programming tasks, macro is not needed and only adds to the complexity. Try (and note how I use the code window - its the {i} above post) to highlight code and retain formatting:
ods tagsets.excelxp file="/usr/local/SAS/RON/UserDir/Tbl8.xml" style=printer options(sheet_name="Sheet1Ron" embedded_titles='yes' embedded_footnotes='yes'); data want; set sashelp.class; where age in (13,16); run; proc print data=want; by age; title "age : #byval1"; footnote1 '*Height in CM'; footnote2 '*Weight in KG'; run; ods tagsets.excelxP close;
@Ronein wrote:
Sorry but i dont want to change any code in the proc print or macro .
I gave this example because in my real problem i have some macros that creating output tables that need to export
I find results easier to manage if macro definitions and all data manipulation are finished before using the ODS destination for output.
As a minimum once the data sets are set then minor changes to the output destination code is usually easier to implement (and follow later).
Maybe something like:
%macro print_age(age); Proc print data = sashelp.class; Where age = &age. ; title "age : &age." ; footnote1 '*Height in CM'; footnote2 '*Weight in KG'; Run; PROC SQL; create table NoLak as select count(*) as No_Lak from sashelp.class where age = &age. ; QUIT; proc print data=NoLak noobs; run; %mend; ods tagsets.ExcelXP file="/usr/local/SAS/RON/UserDir/Tbl8.XML" style=Printer options(sheet_name="Sheet1Ron" sheet_interval='NONE' embedded_titles='yes' embedded_footnotes='yes') ; %print_age(13); ods tagsets.excelxp options(sheet_interval='Proc'); ods tagsets.ExcelXP options(sheet_name="Sheet2Ron" sheet_interval='none'); %print_age(16); ods tagsets.ExcelXP close;
note the extra options statement to change the sheet interval option after the first two tables and then reset.
Interleaving the ods destination sheet/ page type options may be a headache depending on destination and how much you drift from the standard appearances.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.