topic ODS EXCEL MULTIPLE TABS in ODS and Base Reporting

ODS EXCEL MULTIPLE TABS

VISHNU239 — Sat, 28 Jul 2018 02:16:19 GMT

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");

Re: ODS EXCEL MULTIPLE TABS

Reeza — Sat, 28 Jul 2018 03:17:50 GMT

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=p09n5pw9ol0897n1qe04zeur27rv.htm&locale=en

@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");

Re: ODS EXCEL MULTIPLE TABS

ballardw — Mon, 30 Jul 2018 15:18:15 GMT

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

Re: ODS EXCEL MULTIPLE TABS

EStrom1 — Thu, 11 Oct 2018 22:40:34 GMT

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.

Re: ODS EXCEL MULTIPLE TABS

Reeza — Thu, 11 Oct 2018 23:05:24 GMT

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.

Re: ODS EXCEL MULTIPLE TABS

EStrom1 — Fri, 12 Oct 2018 15:00:08 GMT

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

Re: ODS EXCEL MULTIPLE TABS

EStrom1 — Fri, 12 Oct 2018 17:00:51 GMT

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.