Side-by-side sgplot in ODS excel

lbarwick — Tue, 27 Jul 2021 15:32:37 GMT

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?

Re: Side-by-side sgplot in ODS excel

Reeza — Tue, 27 Jul 2021 15:37:01 GMT

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."

Re: Side-by-side sgplot in ODS excel

Reeza — Tue, 27 Jul 2021 15:37:52 GMT

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.

Re: Side-by-side sgplot in ODS excel

ballardw — Tue, 27 Jul 2021 16:54:10 GMT

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.

Re: Side-by-side sgplot in ODS excel

lbarwick — Tue, 27 Jul 2021 17:01:36 GMT

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;

topic Re: Side-by-side sgplot in ODS excel in SAS Programming

Side-by-side sgplot in ODS excel

Re: Side-by-side sgplot in ODS excel

Re: Side-by-side sgplot in ODS excel

Re: Side-by-side sgplot in ODS excel

Re: Side-by-side sgplot in ODS excel