topic Re: ODS to generate multi sheet excel sheet via proc report in ODS and Base Reporting

ODS to generate multi sheet excel sheet via proc report

RamKumar — Fri, 31 Oct 2014 09:35:33 GMT

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.

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

Kurt_Bremser — Fri, 31 Oct 2014 09:45:23 GMT

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.

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

RamKumar — Mon, 03 Nov 2014 06:53:25 GMT

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.

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

Kurt_Bremser — Mon, 03 Nov 2014 07:11:59 GMT

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.

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

RamKumar — Mon, 03 Nov 2014 08:35:58 GMT

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.

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

Kurt_Bremser — Mon, 03 Nov 2014 09:01:14 GMT

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.

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

Kurt_Bremser — Mon, 03 Nov 2014 09:05:11 GMT

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.

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

RW9 — Mon, 03 Nov 2014 09:40:57 GMT

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.p df

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

RamKumar — Mon, 03 Nov 2014 11:29:55 GMT

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.

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

Kurt_Bremser — Mon, 03 Nov 2014 12:15:51 GMT

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.