How do I output to an Excel workbook in specific locations (using ODS, Proc Report, Prog GCHART)?

[SASGeek]

Hi all,

Thanks so much for your help in the past. You really are the best-est!

Need your help once most to bring this project home. I need to output the following data (example used). First dataset goes on first sheet, but the graph has to go on the 2nd sheet . I'm also going to need to put another graph right next to it (that's why the repeating gchart code). But, I also need a bunch of proc report outputs (need to do highlighting, etc)  on the same sheet but starting in different locations. Here are my attempts but it doesn't work.

Thanks so much!

data example;

set sashelp.cars (obs=10); 

run;

data test;

    input rating $ count;

    datalines;

    Amber 1000

    Green 450

    Red   6445

    ;

run; 

ods escapechar='^'; 

ods excel file = '/home/mine/test_report.xlsx';

ods excel options (sheet_name="First Sheet");

proc report data=example missing nowd;

column model msrp invoice;

run;

ods excel options (sheet_name="Second Sheet");

ods graphics / reset  outputfmt=jpg;

pattern1 color= &amber.; /* Amber */

pattern2 color= &green.;  /* Green */

pattern3 color= &red.;    /* Red */

proc gchart data=test; 

title "This is the first chart";

format count comma10.0;

pie rating /noheading

sumvar=count

value=arrow

plabel=(font = 'Times New Roman/bold' height = 13pt )

percent=arrow;

run;

quit;

ods graphics / reset  outputfmt=jpg;

pattern1 color=  yellow;

pattern2 color= green;

pattern3 color= red;

proc gchart data=test; 

title "This will be another chart but needs to be next to the first";

format count comma10.0;

pie rating /noheading

sumvar=count

value=arrow

plabel=(font = 'Times New Roman/bold' height = 13pt )

percent=arrow;

run;

quit;

ods excel options (sheet_interval='none' start_at="A43");

proc report data=example missing nowd;

    columns model cylinders Horsepower;

run;

ods excel options (sheet_interval='none' start_at="F43");

proc report data=example missing nowd;

    columns model weight length;

run;

ods excel close;

[PaigeMiller]

ODS Excel does not let you place two graphs side-by-side. It can only place two plots one above the other.

You could get SAS two place two graphs side-by-side and then output this to an Excel sheet using ODS Excel, so they appear in Excel side by side. I have used SAS to put two graphs side by side, but I have not used ODS EXCEL to get the two graphs into Excel. See: https://blogs.sas.com/content/iml/2022/07/13/ods-graphs-panel.html

[SASGeek]

Good idea! Thank you!

Any suggestions on how to output PROC REPORT to a specific cell? Couldn't find anything in the papers that I could get to work.

[PaigeMiller]

---

@SASGeek wrote:

Any suggestions on how to output PROC REPORT to a specific cell? 

---

This is not a capability of ODS EXCEL. Whatever you output via ODS Excel goes underneath  whatever has been previously output to that Excel worksheet (with a blank row in between the previous ODS Excel output and the new ODS Excel output). If nothing previously has been output to that Excel worksheet, then ODS Excel puts the output at the top left of the sheet.

[PaigeMiller]

---

@SASGeek wrote:

Any suggestions on how to output PROC REPORT to a specific cell?

Perhaps you can fake this by having blank rows and columns in PROC REPORT (something I have never tried) so you could force the output to Excel to appear to begin in cell C6. But if there is already something else written to this Excel tab, even that won't work.

[Ksharp]

You could make a picture to put these two graphs side by side and insert it into Excel by these two skills:

https://blogs.sas.com/content/graphicallyspeaking/2022/09/10/complex-layouts-using-the-sg-procedures...

https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

data example;
set sashelp.cars (obs=10); 
run;
 
data test;
    input rating $ count;
    datalines;
    Amber 1000
    Green 450
    Red   6445
    ;
run;


ods _all_ close;
options nodate nonumber;
options leftmargin="0.001in" rightmargin="0.001in";
options papersize=(7.35in 3.00in);

title;
ods printer printer=png300 file="%sysfunc(pathname(work))\dashboard.png" style=normalprinter;

ods layout gridded columns=2 advance=proc
    column_gutter=0.1in row_gutter=0.1in;

ods graphics / width=3.5in noborder;
title;
proc sgpie data=test;
donut rating / response=count holevalue datalabeldisplay=(percent)
               holelabel="Total Sales" datalabelloc=outside;
run;
proc sgpie data=test;
pie rating / response=count  datalabeldisplay=(percent)
                datalabelloc=inside;
run;
ods layout end;
ods printer close;






ods excel file="c:\temp\temp.xlsx" ;
ods excel options (sheet_name="First Sheet");
 
proc report data=example missing nowd;
column model msrp invoice;
run;
 
ods excel options (sheet_name="Second Sheet"  sheet_interval="none");
goptions iback="%sysfunc(pathname(work))\dashboard.png" imagestyle=fit vsize=3.00in hsize=7.35in;

proc gslide;
run;
 

ods excel options ( start_at="20,20");
proc report data=example missing nowd;
    columns model cylinders Horsepower;
run;
ods excel options (start_at="35,6");
proc report data=example missing nowd;
    columns model weight length;
run;
ods excel close;

[Kathryn_SAS]

Here is another blog that might be helpful:

https://blogs.sas.com/content/sgf/2019/04/23/ods-excel-control/