Side-by-side sgplot in ODS excel

[lbarwick]

I am using ODS excel destination to create a worksheet with a summary table at the top and two distinct bar charts below. So in my ods excel step I have a proc report and two distinct proc sgplots. I am trying to use the ods excel option "start_at=" to control where the two graphs display on the excel worksheet. I want the first graph to be placed at cell B10 and the second graph to be placed at cell G10,but the second graph always is placed immediately below the first graph at cell B34. Any thoughts?

[Reeza]

Unfortunately the START_AT option only works for the first table/graph. This is sort of mentioned in the documentation.

"Tip: This option cannot be changed in the middle of a sheet."

[Reeza]

A possible workaround, use SGTEMPLATE to make a custom template that has both graphs side by side. You can use the TMPLOUT option on SGPLOT to get the template graph code for each graph.

[ballardw]

Another option may be to use Proc SGPanel but typically that requires restructuring data.

If you show the current SGPlot code you are using we may be able to make suggestions if that is possible.

[lbarwick]

ods excel file="&dl\Reports\&xlsxfiledate._ClinOps_Openings.xlsx" style=myweb options(gridlines="on" flow="tables")

options(suppress_bylines="yes");
ods excel
options(
sheet_interval="none"
frozen_headers="0"
frozen_rowheaders="0"
autofilter="none"
suppress_bylines="yes"
start_at="B2"
sheet_name="Req Summary &xlsxfiledate");
proc report data=req_summary nowd style(header)={just=l background=MOLG foreground=black font_weight=bold}; 
column servicearea totreq us india canada eu uk;

 define servicearea / display 'Service Area' style(column)={just=left cellwidth=110pt}; 
 define totreq / display 'Open Requisitions' style(column)={just=left cellwidth=80pt}; 
 define us / display 'US' style(column)={just=left cellwidth=50pt}; 
 define india / display 'India' style(column)={just=left cellwidth=50pt}; 
 define canada / display 'Canada' style(column)={just=left cellwidth=50pt}; 
 define eu / display 'EU' style(column)={just=left cellwidth=50pt}; 
 define uk / display 'UK' style(column)={just=left cellwidth=50pt}; 

compute servicearea;
    	if servicearea='Clinical Project Coordination' then call define(_row_, "style", "STYLE=[background=&cpccolor]");
	  	if servicearea='Clinical Research Associate' then call define(_row_, "style", "STYLE=[background=&cracolor]");
	  	if servicearea='Clinical Study Management' then call define(_row_, "style", "STYLE=[background=&csmcolor]");
	  	if servicearea='Data Management' then call define(_row_, "style", "STYLE=[background=&dmcolor]");
	  	if servicearea='Trial Master File' then call define(_row_, "style", "STYLE=[background=&tmfcolor]");
	  	if servicearea='Total' then call define(_row_, "style", "STYLE=[background=MOLG font_weight=bold]");
	endcomp;
run;
ods graphics on / width = 5 in;
proc sort data=globalopenings1; by locationorder; run;

ods excel options(start_at="B10" sheet_interval="none");
proc sgplot data=globalopenings1;
	vbar servicearea / response=openings1 group=locationorder grouporder=ascending DATALABELFITPOLICY=NONE datalabel datalabelattrs=(size=10 family=arial color=black)
	  stat=sum dataskin=matte;
	  styleattrs datacolors=(&cpccolor &cracolor &csmcolor &dmcolor &tmfcolor);
	  xaxis grid display=(noticks nolabel);
  	  yaxis GRID VALUES = (0 TO 25 BY 5) label="Open Reqs";
	  keylegend / title='';
   	  format locationorder locationorder.;
run;

proc format;
value saorder 1='Clinical Project Coordination' 2='Clinical Research Associate' 3='Clinical Study Management' 4='Data Management' 5='Trial Master File';
run;

proc sort data=globalopenings1; by saorder; run;

ods excel options(start_at="G10" sheet_interval="none");
proc sgplot data=globalopenings1;
	vbar location / response=openings1 group=saorder grouporder=ascending DATALABELFITPOLICY=NONE datalabel datalabelattrs=(size=10 family=arial color=black)
	  stat=sum dataskin=matte;
	  styleattrs datacolors=(&cpccolor &cracolor &csmcolor &dmcolor &tmfcolor);
	  xaxis grid values = ("US" "India" "Canada" "EU" "UK") display=(noticks nolabel);
  	  yaxis GRID VALUES = (0 TO 20 BY 5) label="Open Reqs";
	  keylegend / title='';
	  format saorder saorder.;
run;
quit;
ods excel close;