Creating a excel documents with multiple sheets

[Sennahlake]

Hi,

I have a problem with an output to excel from SAS.

I want my output to be one excel document with multiple sheets, and my thoughts were to use ods tagsets.excelxp with a proc report to define and put different styles on my variables.

It’s not a problem to use ods tagsets.excelxp if I know exactly how many sheets I need, my problem is that I don’t always know how many sheets I need when I get an order from a client.
And when I tried to put the code in a macro I failed and only get 1 sheet.

I have used the following code to get an output in the way I want it to be. To simplify my post I have used a dataset from the libname sashelp.

And to solve my problem I would like to know how I put the following code so that each car model gets a sheet in a single Excel document.

options missing='';
ods listing close;
ods tagsets.excelxp file='C:/_localdata/sample.xls' style=styles.journal
options(sheet_name="Acura");

proc report data=sashelp.cars nowd completerows contents="" nocenter
style(report)=[frame=above rules=groups cellspacing=1 font_size=1]
style(column)=[cellspacing=1 font_face=arial cellheight=28]
style(header)=[frame=hsides backgroundcolor=#99CCFF borderbottomcolor=black font_face=arial just=c];
where make="Acura";
columns make model type origin Invoice;
define make/"Make" style(column)={cellwidth=200} style=[font_weight=bold];
define model/ "Model" style(column)={cellwidth=200} style=[font_weight=bold];
define type/ "type" style(column)={cellwidth=200};
define origin/ "origin" style(column)={cellwidth=200};
define Invoice/"Invoice" style(column)={cellwidth=200};
run; quit;
        
ods tagsets.excelxp options(sheet_name="Audi");
proc report data=sashelp.cars nowd completerows contents="" nocenter
style(report)=[frame=above rules=groups cellspacing=1 font_size=1]
style(column)=[cellspacing=1 font_face=arial cellheight=28]
style(header)=[frame=hsides backgroundcolor=#99CCFF borderbottomcolor=black font_face=arial just=c];
where make="Audi";
columns make model type origin Invoice;
define make/"Make" style(column)={cellwidth=200} style=[font_weight=bold];
define model/ "Model" style(column)={cellwidth=200} style=[font_weight=bold];
define type/ "type" style(column)={cellwidth=200};
define origin/ "origin" style(column)={cellwidth=200};
define Invoice/"Invoice" style(column)={cellwidth=200};
run; quit;

ods tagsets.excelxp close;
ods listing;

Thanks

Best Regards

Hannes

[RW9]

Hi,

Firstly the solution you want is to use by group processing and the sheet_interval option:

options missing='';
ods listing close;
ods tagsets.excelxp file='C:/_localdata/sample.xls' style=styles.journal options(sheet_interval="Bygroup");

proc report data=sashelp.cars nowd completerows contents="" nocenter
            style(report)=[frame=above rules=groups cellspacing=1 font_size=1]
            style(column)=[cellspacing=1 font_face=arial cellheight=28]
            style(header)=[frame=hsides backgroundcolor=#99CCFF borderbottomcolor=black font_face=arial just=c];
  by make;
  columns model type origin Invoice;
  define model/ "Model" style(column)={cellwidth=200} style=[font_weight=bold];
  define type/ "type" style(column)={cellwidth=200};
  define origin/ "origin" style(column)={cellwidth=200};
  define Invoice/"Invoice" style(column)={cellwidth=200};
run; 

ods tagsets.excelxp close;

All options can be found in the documentation:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

You could also do it with a data _null_ call execute() setup, but that is more complicated, and only for certain situations.  Why do you not know up front how many sheets there are, surely you have the data?  I wouldn't recommend a macro for this, you will spend more time maintaing that than just typing out some code.

[Sennahlake]

Thanks, this solution worked really well, but I would prefer not to have "Make=Audi" in the example. I would like to have the solution of just having "Audi", do you know that this is possible?

Also, the rows that were for example in the first group (Make=Audi) are now blank in the next sheet. Is there a way to remove these so that it just shows the group that is selected? I've included two pictures to show what I mean.

Well I know how many sheet I have to work with, but not when I'm testing my code, as this is for a future project.

[RW9 · Accepted solution]

Just mess around with the options provided in the link:

options missing='';
ods listing close;
ods tagsets.excelxp file='s:\temp\rob\sample.xls' style=styles.statistical options(sheet_interval="Bygroup" suppress_bylines="yes" sheet_label=" ");

proc report data=sashelp.cars nowd nocenter;
  by make;
  columns model type origin Invoice;
  define model    / "Model" style(column)={cellwidth=200} style=[font_weight=bold];
  define type     / "type" style(column)={cellwidth=200};
  define origin   / "origin" style(column)={cellwidth=200};
  define Invoice  /"Invoice" style(column)={cellwidth=200};
run; 

ods tagsets.excelxp close;

[Sennahlake]

Thanks alot for the solution!

But just one last thing

Is it posible to add another table in the same sheet?

For example, I have tried to use the following code, but the tables appear in different sheets (Audi, Acura[..], Audi2, Acura2...)

options missing='';
ods listing close;
ods tagsets.excelxp file='s:\temp\rob\sample.xls'
style=styles.journal options(sheet_interval="Bygroup"
suppress_bylines="yes" sheet_label=" ");

proc report data=sashelp.cars nowd nocenter
            style(report)=[frame=above rules=groups cellspacing=1 font_size=1]
            style(column)=[cellspacing=1 font_face=arial cellheight=28]
            style(header)=[frame=hsides backgroundcolor=#99CCFF borderbottomcolor=black font_face=arial just=c];
  by make;
  columns model type origin Invoice;
  define model/ "Model" style(column)={cellwidth=200} style=[font_weight=bold];
  define type/ "type" style(column)={cellwidth=200};
  define origin/ "origin" style(column)={cellwidth=200};
  define Invoice/"Invoice" style(column)={cellwidth=200};
run;


proc report data=sashelp.cars nowd nocenter
            style(report)=[frame=above rules=groups cellspacing=1 font_size=1]
            style(column)=[cellspacing=1 font_face=arial cellheight=28]
            style(header)=[frame=hsides backgroundcolor=#99CCFF borderbottomcolor=black font_face=arial just=c];
  by make;
  columns model Horsepower MPG_City MPG_Highway Weight;
  define model/ "Model" style(column)={cellwidth=200} style=[font_weight=bold];
  define Horsepower/ "Horsepower" style(column)={cellwidth=200} style=[font_weight=bold];
  define MPG_City/ "MPG_City" style(column)={cellwidth=200};
  define MPG_Highway/ "MPG_Highway" style(column)={cellwidth=200};
  define Weight/"Weight" style(column)={cellwidth=200};
run;


ods tagsets.excelxp close;

[ballardw]

The option

sheet_interval="Bygroup"

is what places each BY group table on a separate sheet.

If you are contemplating having a specific subsiet , say 3 makes on one page you would be best off adding a separate group variable and a PAGE variable.

If you only want two tables then instead sheet_intervl="BYGROUP" use "PROC" and a where clause to subset the data. Which is cumbersome if you are trying to create groups, see previouls line on adding group variable.