topic Re: How to create multiple excel graph sheets in one workbook using proc sgplot in SAS Programming

How to create multiple excel graph sheets in one workbook using proc sgplot

mmohotsi — Tue, 04 Apr 2023 08:11:45 GMT

Good day

I am trying to create multiple sheets using ods excel in proc sgplot. The code is as follows.

%Let Dated = '10Feb2023'd;
%let date1=%sysfunc(today(),date9.);
%Let reports = <path>;
ods _all_ close;
ODS excel FILE = "&reports\Blending &date1 .xlsx";
OPTIONS PAGESIZE=MAX ORIENTATION=LANDSCAPE;
ods graphics on / width=11in height=6in noscale;

ods excel options(autofilter="ALL" sheet_name = "Parameter " embedded_titles='yes');
proc sgplot data=tested (where=(NAME='Parameter' ));
vline dates / response=NUMERIC_ENTRY group=sampling_point groupdisplay=cluster ;
yaxis values=(0 to 200 by 20) label='Parameter' grid LABELATTRS=(weight=bold family=Helvetica size=7)
VALUEATTRS=(family=Helvetica size=7 style=normal weight=normal ) ;

refline &Parameter / axis=y label=('Units') lineattrs=(color=green pattern=dash) labelattrs=(size=7) labelloc=inside;
refline &Dated / axis=x label=('Blending date') lineattrs=(color=red pattern=dash) labelattrs=(size=7) labelloc=inside;
xaxis interval=day VALUESROTATE=VERTICAL
label='Sampled dates' LABELATTRS=(weight=bold size=7) valuesrotate=vertical
VALUEATTRS=(family=arial size=10 style=normal weight=normal )
type=time ;
title1 j=c "Parameter trend" bold;
run;
ods _all_ close;

My challenge is that I could only create 8 excel sheets using the above code.

Is there a way of getting more sheets on the one workbook? I need close to 70 work sheets on one workbook.

Re: How to create multiple excel graph sheets in one workbook using proc sgplot

yabwon — Tue, 04 Apr 2023 12:00:04 GMT

Maybe this will help:

proc sort data = sashelp.cars out = cars;
  by Origin;
run;

ods excel file="R:\text.xlsx"
GFOOTNOTE
GTITLE
options(SHEET_INTERVAL='NONE') /* prevent by group sheet break*/
;
options nobyline;

title1 j=c height=1 font="Courier New" 
  "Title Origin: #byval(Origin).."
;
footnote1 j=c height=1 font="Courier New" 
  "Footnote Origin: #byval(Origin).."
;
proc sgplot data=cars;
  by Origin;
  scatter x=Invoice y=MSRP;
run;


ods excel close;

Bart

Re: How to create multiple excel graph sheets in one workbook using proc sgplot

PaigeMiller — Tue, 04 Apr 2023 13:17:09 GMT

I don't see how the code you present will produce more than 1 plot, but you say it works for up to 8 plots, but not more??? How does this code produce 8 plots? What happens when you ask for more than 8 plots, is there an error in the log? If so, please show us the ENTIRE log for this ODS EXCEL.

Also, please try it with

ods excel options(sheet_interval='PROC');

ods excel options(sheet_interval='PAGE');