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
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;
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.
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.
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;
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;
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: