topic Re: Export in SAS Programming

Export

Ronein — Thu, 12 Apr 2018 13:26:05 GMT

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;

Re: Export

PaigeMiller — Thu, 12 Apr 2018 13:36:07 GMT

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=

Re: Export

RW9 — Thu, 12 Apr 2018 13:50:24 GMT

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;

Re: Export

Ronein — Thu, 12 Apr 2018 13:58:32 GMT

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

Re: Export

ballardw — Thu, 12 Apr 2018 14:53:21 GMT

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