The SAS Output Delivery System and reporting techniques

ODS to generate multi sheet excel sheet via proc report

Reply
Regular Contributor
Posts: 168

ODS to generate multi sheet excel sheet via proc report

I'm using below codes to generate excel sheets via proc report. (SAS EG 5.1 MS Excel 2010)

ODS HTML FILE='/data/indirect_report.xls'  ;

proc report data=rpt.&clint._All_inDir_CbRpt  nowd missing out=work.indirect_report_all ;

title1 'Indirect responses by product offers' ;

columns .....

define ....

run;

ODS HTML FILE='/data/direct_report.xls'  ;

proc report data=rpt.&clint._All_Dir_CbRpt  nowd missing out=work.indirect_report_all ;

title1 'Indirect responses by product offers' ;

columns .....

define ....

run;

ODS HTML FILE='/data/summary_report.xls'  ;

proc report data=rpt.&clint._All_summary_CbRpt  nowd missing out=work.indirect_report_all ;

title1 'Indirect responses by product offers' ;

columns .....

define ....

run;

But I wish to generate the report in a single spreadsheet with multiple tabs instead of multiple excel sheets.e.g 3 ods statement with proc report should produce reports in single worksheet with 3 tabs instead of 3 excel sheets.

Any help would be appreciated.

Super User
Posts: 7,379

Re: ODS to generate multi sheet excel sheet via proc report

Look here:

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

how to do it with Excel tagsets

Save the output file as .xml, so it is clear to all applications what kind of data is inside.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 168

Re: ODS to generate multi sheet excel sheet via proc report

Thanks for your guidance.

Could you please advise how to generate the excel sheet with all the reports in a single tab? e.g. If I use 3 proc report then report should be displayed in a single tab. All the reports should be combined horizontally.

Super User
Posts: 7,379

Re: ODS to generate multi sheet excel sheet via proc report

That's done with the sheet_interval parameter (Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset)

If set to none, all output lands in the currently assigned Tab. Other values will cause automatic new tabs per procedure or by group.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 168

Re: ODS to generate multi sheet excel sheet via proc report

I already tried with the option sheet interval=none and sheet name='summary' for all my 3 proc report, but it is producing excel sheet in three different tabs called summary1,summary2 and summary3 whereas I need all the three reports merged into one tab horizontally.

Super User
Posts: 7,379

Re: ODS to generate multi sheet excel sheet via proc report

If you use one ods tagsets.excelxp statement file='.....' style=.... (sheet_interval='none'); , run your three reports, and then do ods tagsets.excelxp close; you get one tab in your worksheet.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,379

Re: ODS to generate multi sheet excel sheet via proc report

If you really want the reports side by side, you will have to generate a data set first that contains the reports, and then print that to ods tagsets.

So you need to use ods first to output the reports into tables (watch out for different columns names!), merge these

tables sequentially and then use ods tagsets.

IMO, it is easier to do a cut&paste in Excel.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 168

Re: ODS to generate multi sheet excel sheet via proc report

Thanks for your patience and support.

I'm bit confused with your term 'print that to ods tagsets'. May I request you to expand your language in SAS code?

@RW9

From the document, I did tried the code 'ADDING NEW SPREADSHEET AND DATA' and I got the error ERROR: The Excel engine cannot be found since I installed SAS 9.3 with 32-bit in 64-bit machine. Any suggestions to overcome this error? Also it seems this document do not cover the code to produce excel sheet in a named range.

Super User
Posts: 7,379

Re: ODS to generate multi sheet excel sheet via proc report

Print your reports into tables like this:

ods output report=work.output1;

proc report

  data=incidents (

    where=(startweek ge &week - 5)

  )

;

column startweek Low High Critical;

define startweek/order;

define Low/display;

define High/display;

define Critical/display;

run;

ods output close;

This will write a table with columns named like the original column names.

Now suppose a similar report:

ods output report=work.output2 (rename=(

  startweek=startweek1

  low=low1

  high=high1

  critical=critical1

);

proc report

  data=incidents2 (

    where=(startweek ge &week - 5)

  )

;

column startweek Low High Critical;

define startweek/order;

define Low/display;

define High/display;

define Critical/display;

run;

ods output close;

Now you can merge them:

data all;

merge

  output1

  output2

;

run;

Now you have the numbers in one table, side by side, after that do:

ods tagsets.excelxp file="......";

proc print data=all noobs;

run;

ods tagsets.excelxp close;

By using proc report in the final step, you may be able to add style definitions.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,675

Re: ODS to generate multi sheet excel sheet via proc report

Why not create (and this isn't my preferred option) an empty spreadsheet.  Setup the tabs as you want, formatted etc. then use the libname statement to insert the data into those named ranges.  Example: http://www2.sas.com/proceedings/sugi31/024-31.pdf

Ask a Question
Discussion stats
  • 9 replies
  • 2571 views
  • 3 likes
  • 3 in conversation