BookmarkSubscribeRSS Feed
RamKumar
Fluorite | Level 6

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.

9 REPLIES 9
RamKumar
Fluorite | Level 6

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.

RamKumar
Fluorite | Level 6

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.

Kurt_Bremser
Super User

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.

Kurt_Bremser
Super User

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.

RamKumar
Fluorite | Level 6

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.

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 5493 views
  • 3 likes
  • 3 in conversation