Exporting from SAS Enterprise Guide to multiple tabs in Excel (.xlsx)

Question

I am trying to export output from SAS Enterprise Guide to Microsoft Excel 2010 (.xlsx), outputting both multiple tables per sheet, and several sheets per workbook. I want to have several proc sql outputs exported to a single tab, as well as other output to several tabs within the same worksheet. I have code that allows me to do this (see below), but I can't get it to export across multiple programs within SAS Enterprise Guide. Ideally, I would like each program of my SAS Enterprise Guide process flow to export output to a different tab within the same Excel file. For example, the below code works when it is all in the same program, but I want the "ID and Age" proc sql code to be in a different program then the "Diagnosis and Treatment" code, for the purposes of my own organization.

Is there a way to do this?

So far when I try to split the below code into two programs, it just replaces the first output with the second, instead of creating two separate sheets.

goptions device=actximg; /*suppresses Error*/
ods excel file="C:\Example.xlsx" 
style=htmlblue
options(
  sheet_interval="none" 
  sheet_name="ID and Age"
  embedded_titles='yes'
 );

proc sql;title "Patient list";
select patient_id, date from data;run;

proc sql;title "Patients under 2";
select patient_id, dob, age_in_years from data where age_in_years < 2;run;


ods excel
options(
  sheet_interval="none" 
  sheet_name="Diagnosis and Treatment"
  embedded_titles='yes'
 );

proc sql;title "Diagnosis for patients under 2";
select patient_id, diagnosis from data where age_in_years < 2;run;

proc sql;title "Treatment for patients under 2";
select patient_id, treatment from data where age_in_years < 2;run;

ods excel close;

Answer 1

It won't be possible to keep the ODS EXCEL destination open across multiple programs in Enterprise Guide, unfortunately. Each program starts and ends with ODS _ALL_ CLOSE;, which closes the destination, and since ODS isn't capable of updating a spreadsheet in place (yet?), that precludes the usage you're suggesting.

What you could do, however, is set your output up as macro(s). Then have a single program that calls all of those macros, within ods excel blocks. That would allow you to do what you're looking to - at the cost of having all of those datasets and outputs in one final output program rather than having them in seperate programs.

Or, you could perform all of the calculations in separate programs, but then have one master output program that outputs all of the final results (which are saved either in datasets or ODS DOCUMENT objects).