ODS EXCEL MULTIPLE TABS

[VISHNU239]

Hi, All.

I have an issue while exporting proc report to excel using ODS. I am using a macro which has all the names of individual persons and individual excel tabs needs to be created for every unique person in a single excel file.

 I am using the below code where it is generating only one excel tab  for only the last person in the macro . I am giving macro name in the sheet_name option. I want to create individual excel tabs for every person in the macro. please help me.

ods excel file="\c\desktop\output\temp.xlsx" options (sheet_interval="none" sheet_name="&Name" embedded_titles="YES");

[Reeza]

Any reason you can't just use by group processing?

proc sort data=sashelp.cars out=cars; by make;
run;

ods excel file='/folders/myfolders/demo.xlsx' options (sheet_interval='bygroup');

proc print data=cars;
by make;
run;

ods excel close;

Anyways, if you can't for some reason you can control the sheet interval with the Sheet_interval and Sheet_label options in the ODS OPTIONS. 

http://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=odsug&docsetTarget=p09n5pw9ol0...

---

@VISHNU239 wrote:

Hi, All.

I have an issue while exporting proc report to excel using ODS. I am using a macro which has all the names of individual persons and individual excel tabs needs to be created for every unique person in a single excel file.

 I am using the below code where it is generating only one excel tab  for only the last person in the macro . I am giving macro name in the sheet_name option. I want to create individual excel tabs for every person in the macro. please help me.

ods excel file="\c\desktop\output\temp.xlsx" options (sheet_interval="none" sheet_name="&Name" embedded_titles="YES");

---

[ballardw]

---

@VISHNU239 wrote:

Hi, All.

I have an issue while exporting proc report to excel using ODS. I am using a macro which has all the names of individual persons and individual excel tabs needs to be created for every unique person in a single excel file.

 I am using the below code where it is generating only one excel tab  for only the last person in the macro . I am giving macro name in the sheet_name option. I want to create individual excel tabs for every person in the macro. please help me.

ods excel file="\c\desktop\output\temp.xlsx" options (sheet_interval="none" sheet_name="&Name" embedded_titles="YES");

---

What do you think the option sheet_interval="none" does? Consider: SHEET=TAB in concept. Then read that option carefully.

BY groups are almost always the way to go unless you have two or more different elements per "tab" that come from different procedures.

[EStrom1]

I have the same issue as the original post. I am converting an existing macro from ods tagsets excelxp to ods excel, but when I change to ods excel now everything is getting dumped into a single tab.  

The code uses syntax like this;

ods excel 
  file = "blah.xlsx"
  options(
    sheet_interval ='none'
    sheet_name ='Tab1Name'
  )
;

{proc report here}

ods excel 
  options(
    sheet_interval ='none'
    sheet_name ='Tab2Name'
  )
;

{another proc report here}

etc.

In this case, everything is dumped to Tab1Name and Tab2Name is not getting created.

[Reeza]

This was a bug in the early version of SAS with ODS EXCEL. The easiest best fix is an upgrade. Otherwise, I have posted a workaround on the forums here somewhere.

[EStrom1]

Thanks.  I'm running 9.4 M5.  I thought this was the latest maintenance release, no?

[EStrom1]

Ok, got the answer from SAS tech support.  I need to remove the sheet_interval="none" option.  Although this worked with ExcelXP tagset, with ODS Excel, this option is telling SAS to put everything on one tab.  I could manually force a new tab with a sheet_interval="now" option; however this is the default behavior, so if I just avoid the sheet_interval="none" statement, it does the trick.  

So the code should look like this 

ods excel 
  file = "blah.xlsx"
  options(
    sheet_name ='Tab1Name'
  )
;

{proc report here}

ods excel 
  options(
    sheet_name ='Tab2Name'
  )
;

{another proc report here}

etc.