BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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;

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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=

--
Paige Miller
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Meteorite | Level 14
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
ballardw
Super User

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

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
  • 4 replies
  • 677 views
  • 0 likes
  • 4 in conversation