I used the folowing code to have pdf output containing both tables and graphs.
goptions device=png;
ods pdf file="&Drive./Output.pdf" STARTPAGE= NEVER;
The STARTPAGE= NEVER options works fine with tables but the graph is overlaped.
Any ideas on solving this?
Thank you!
You would have to show the entire program between the ODS PDF and ODS PDF Close statements to diagnose something like this. But likely you need to insert an ODS PDF startpage=now; before the graphics and possibly startpage = yes if there are more than one graphs generated.
Hi Ballardw,
I did tried your suggestion by adding ODS PDF startpage=now; in form of the graph. However seems not work. The full code is attached below.
****************************************************************************************************************;
%Macro LOOP_OUTPUT;
%LET DSID=%SYSFUNC(OPEN(Eventdir.range,IN));
%LET NOBS=%SYSFUNC(ATTRN(&DSID,NOBS));
%IF &DSID > 0 %THEN %LET RC=%SYSFUNC(CLOSE(&DSID));
%IF NOBS>0 %THEN %DO;
proc sql; create table Eventdir.Group_list as select distinct Group from Eventdir.range; quit;
data Eventdir.Group_list; set Eventdir.Group_list; id=_n_;run;
%put _n_;
data _NULL_;
if 0 then set Eventdir.Group_list nobs=n;
call symputx('nrows',n);stop; run;
%DO I=1 %TO &nrows;
proc sql noprint; select distinct Group into :Group_macro from Eventdir.Group_list where id=&I.;quit;
%put &Group_macro;
%put "**********************************************************";
%put I_&I.;
%put "**********************************************************";
proc sql;
create table Eventdir.ORange_&I as
select * from Eventdir.range
where Group="&Group_macro" ;
quit;
proc sql;
create table Eventdir.OEstimate_&I as
select * from Eventdir.Estimate
where Group="&Group_macro" ;
quit;
goptions device=png;
ods pdf file="&Drive./Output";
proc report data=Eventdir.ORange_&I nowd; title "&Title Range";
compute result;
if strip(result)='Out of Rang -Red' then do;
call define(_row_, "style", "style=[backgroundcolor=red]");
call define(_row_, "style", "style=[backgroundcolor=red]");
end;
if strip(result)='Out of Rang -Yellow' then do;
call define(4, "style", "style=[backgroundcolor=yellow]");
call define(12, "style", "style=[backgroundcolor=yellow]");
end;
endcomp;
run;
proc print data=Eventdir.OEstimate_&I;title "&Title Estimate";
run;
/* %XLS_EXPORT ( Eventdir.Output, "&supdata.&Group_macro._NESTED_ACC_SUPP.xlsx", "&Group_macro.");*/
%_eg_conditional_dropds(WORK.SORTTempTableSorted);
/* -------------------------------------------------------------------
Sort data set EVENTDIR.RANGE
------------------------------------------------------------------- */
/* FOR MANUAL RUN %let I = 99; */
PROC SORT
DATA=EVENTDIR.ORange_&I(KEEP=period Distance AdjAvg Count Group LLyellow ULyellow LLred ULred)
OUT=WORK.SORTTempTableSorted
;
BY Group;
RUN;
PATTERN1 COLOR=CXd7d7d7;
PATTERN2 COLOR = _STYLE_;
PATTERN3 COLOR = _STYLE_;
PATTERN4 COLOR = _STYLE_;
PATTERN5 COLOR = _STYLE_;
PATTERN6 COLOR = _STYLE_;
PATTERN7 COLOR = _STYLE_;
PATTERN8 COLOR = _STYLE_;
PATTERN9 COLOR = _STYLE_;
PATTERN10 COLOR = _STYLE_;
PATTERN11 COLOR = _STYLE_;
PATTERN12 COLOR = _STYLE_;
SYMBOL1
INTERPOL=JOIN
HEIGHT=10pt
VALUE=SQUARE
CV=CXFF9900
LINE=2
WIDTH=2
CI=CXFF9900
;
SYMBOL2
INTERPOL=JOIN
HEIGHT=10pt
VALUE=CIRCLE
CV=CX001aaa
CO=WHITE
LINE=1
WIDTH=4
CI=CX001aaa
;
SYMBOL3
INTERPOL=JOIN
HEIGHT=10pt
VALUE=Triangle
CV= cxfff700
CO=WHITE
LINE=2
WIDTH=2
CI=cxfff700
;
SYMBOL4
INTERPOL=JOIN
HEIGHT=10pt
VALUE=Triangle
CV=cxfff700
CO=WHITE
LINE=2
WIDTH=2
CI=cxfff700
;
SYMBOL5
INTERPOL=JOIN
HEIGHT=10pt
VALUE=Triangle
CV= cxff0000
CO=WHITE
LINE=2
WIDTH=2
CI=cxff0000
;
SYMBOL6
INTERPOL=JOIN
HEIGHT=10pt
VALUE=Triangle
CV=cxff0000
CO=WHITE
LINE=2
WIDTH=2
CI=cxff0000
;
Legend2
FRAME
;
Legend1
FRAME
;
Axis1
STYLE=1
WIDTH=1
MINOR=NONE
;
Axis2
STYLE=1
WIDTH=1
;
Axis3
STYLE=1
WIDTH=1
MINOR=
(NUMBER=1
)
LABEL=("Count")
;
TITLE;
TITLE1 "Distance Bar and &Title Line Plot";
FOOTNOTE;
FOOTNOTE1 "Generated on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";
ODS PDF startpage=now;
PROC GBARLINE DATA=WORK.SORTTempTableSorted
;
BAR period
/
SUMVAR=Distance
CLIPREF
FRAME TYPE=SUM
COUTLINE=BLACK
RAXIS=AXIS1
MAXIS=AXIS2
LREF=1
CREF=CXDDDD00
/*REF=3.5 2.3 -2.3 -3.5*/
LEGEND=LEGEND2
;
PLOT / SUMVAR=AdjAvg
TYPE=SUM
CLIPREF
AXIS=AXIS3
LEGEND=LEGEND1
;
PLOT / SUMVAR=Count
TYPE=SUM
CLIPREF
AXIS=AXIS3
;
PLOT / SUMVAR=LLyellow
TYPE=SUM
CLIPREF
AXIS=AXIS3
LEGEND=LEGEND1
;
PLOT / SUMVAR=ULyellow
TYPE=SUM
CLIPREF
AXIS=AXIS3
LEGEND=LEGEND1
;
PLOT / SUMVAR=LLred
TYPE=SUM
CLIPREF
AXIS=AXIS3
LEGEND=LEGEND1
;
PLOT / SUMVAR=ULred
TYPE=SUM
CLIPREF
AXIS=AXIS3
LEGEND=LEGEND1
;
BY Group;
/* -------------------------------------------------------------------
End of task code
------------------------------------------------------------------- */
RUN; QUIT;
%_eg_conditional_dropds(WORK.SORTTempTableSorted);
TITLE; FOOTNOTE;
PATTERN1;
GOPTIONS RESET = SYMBOL;
%END;
%end;
%mend LOOP_OUTPUT;
%LOOP_OUTPUT;
Please provide details of "seems not to work". No output? Unexpected output? or what. I suspect in this case it is that the first two tables no longer appear on the same page. If so say so.
Since I do not see an ODS PDF Close anywhere I suspect your program is incomplete. There is chance that the previous document did not get replaced yet.
Also I do not see the Startpage=never. If the "not working" is the tables appearing on separate pages it is because this is missing. You mix in the Startpage = never, Startpage=now and such as needed in the body of the program
Here are a couple of suggestions. First ignore the macro. Write the code for one set of tables and the graph.
Then run as this skeleton:
ods pdf file="some file goes here" startpage=never;
proc report ....
proc print ....
ods pdf startpage=now;
proc gbarline ...
ods pdf close;
Then see how that works. Get this working before attempting to loop the code.
Since you have a macro %_eg_conditional_dropds(WORK.SORTTempTableSorted); that is not defined in the code I have no idea what impact that might have.
Hi ballardw, Thanks for replying.
I updted the code as below. The pdf output remains the same: The tables works good without pagebreak between them. But the graph still overlaped on the tables as the screenshot I attached before.
%Macro LOOP_OUTPUT;
%LET DSID=%SYSFUNC(OPEN(Eventdir.range,IN));
%LET NOBS=%SYSFUNC(ATTRN(&DSID,NOBS));
%IF &DSID > 0 %THEN %LET RC=%SYSFUNC(CLOSE(&DSID));
%IF NOBS>0 %THEN %DO;
proc sql; create table Eventdir.Group_list as select distinct Group from Eventdir.range; quit;
data Eventdir.Group_list; set Eventdir.Group_list; id=_n_;run;
%put _n_;
data _NULL_;
if 0 then set Eventdir.Group_list nobs=n;
call symputx('nrows',n);stop; run;
%DO I=1 %TO &nrows;
proc sql noprint; select distinct Group into :Group_macro from Eventdir.Group_list where id=&I.;quit;
%put &Group_macro;
%put "**********************************************************";
%put I_&I.;
%put "**********************************************************";
proc sql;
create table Eventdir.ORange_&I as
select * from Eventdir.range
where Group="&Group_macro" ;
quit;
proc sql;
create table Eventdir.OEstimate_&I as
select * from Eventdir.Estimate
where Group="&Group_macro" ;
quit;
goptions device=png;
ods pdf file="&Drive./Output" startpage=never;
proc report data=Eventdir.ORange_&I nowd; title "&Title Range";
compute result;
if strip(result)='Out of Rang -Red' then do;
call define(4, "style", "style=[backgroundcolor=red]");
call define(_row_, "style", "style=[backgroundcolor=red]");
end;
if strip(result)='Out of Rang -Yellow' then do;
call define(4, "style", "style=[backgroundcolor=yellow]");
call define(12, "style", "style=[backgroundcolor=yellow]");
end;
endcomp;
run;
proc print data=Eventdir.OEstimate_&I;title "&Title Estimate";
run;
/* %XLS_EXPORT ( Eventdir.Output, "&supdata.&Group_macro._NESTED_ACC_SUPP.xlsx", "&Group_macro.");*/
%_eg_conditional_dropds(WORK.SORTTempTableSorted);
/* -------------------------------------------------------------------
Sort data set EVENTDIR.RANGE
------------------------------------------------------------------- */
/* FOR MANUAL RUN %let I = 99; */
ods pdf startpage=now;
PROC SORT
DATA=EVENTDIR.ORange_&I(KEEP=period Distance AdjAvg Count Group LLyellow ULyellow LLred ULred)
OUT=WORK.SORTTempTableSorted
;
BY Group;
RUN;
PATTERN1 COLOR=CXd7d7d7;
PATTERN2 COLOR = _STYLE_;
PATTERN3 COLOR = _STYLE_;
PATTERN4 COLOR = _STYLE_;
PATTERN5 COLOR = _STYLE_;
PATTERN6 COLOR = _STYLE_;
PATTERN7 COLOR = _STYLE_;
PATTERN8 COLOR = _STYLE_;
PATTERN9 COLOR = _STYLE_;
PATTERN10 COLOR = _STYLE_;
PATTERN11 COLOR = _STYLE_;
PATTERN12 COLOR = _STYLE_;
SYMBOL1
INTERPOL=JOIN
HEIGHT=10pt
VALUE=SQUARE
CV=CXFF9900
LINE=2
WIDTH=2
CI=CXFF9900
;
SYMBOL2
INTERPOL=JOIN
HEIGHT=10pt
VALUE=CIRCLE
CV=CX001aaa
CO=WHITE
LINE=1
WIDTH=4
CI=CX001aaa
;
SYMBOL3
INTERPOL=JOIN
HEIGHT=10pt
VALUE=Triangle
CV= cxfff700
CO=WHITE
LINE=2
WIDTH=2
CI=cxfff700
;
SYMBOL4
INTERPOL=JOIN
HEIGHT=10pt
VALUE=Triangle
CV=cxfff700
CO=WHITE
LINE=2
WIDTH=2
CI=cxfff700
;
SYMBOL5
INTERPOL=JOIN
HEIGHT=10pt
VALUE=Triangle
CV= cxff0000
CO=WHITE
LINE=2
WIDTH=2
CI=cxff0000
;
SYMBOL6
INTERPOL=JOIN
HEIGHT=10pt
VALUE=Triangle
CV=cxff0000
CO=WHITE
LINE=2
WIDTH=2
CI=cxff0000
;
Legend2
FRAME
;
Legend1
FRAME
;
Axis1
STYLE=1
WIDTH=1
MINOR=NONE
;
Axis2
STYLE=1
WIDTH=1
;
Axis3
STYLE=1
WIDTH=1
MINOR=
(NUMBER=1
)
LABEL=("Count")
;
TITLE;
TITLE1 "Distance Bar and &Title Line Plot";
FOOTNOTE;
FOOTNOTE1 "Generated on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";
ODS PDF startpage=now;
PROC GBARLINE DATA=WORK.SORTTempTableSorted
;
BAR period
/
SUMVAR=Distance
CLIPREF
FRAME TYPE=SUM
COUTLINE=BLACK
RAXIS=AXIS1
MAXIS=AXIS2
LREF=1
CREF=CXDDDD00
/*REF=3.5 2.3 -2.3 -3.5*/
LEGEND=LEGEND2
;
PLOT / SUMVAR=AdjAvg
TYPE=SUM
CLIPREF
AXIS=AXIS3
LEGEND=LEGEND1
;
PLOT / SUMVAR=Count
TYPE=SUM
CLIPREF
AXIS=AXIS3
;
PLOT / SUMVAR=LLyellow
TYPE=SUM
CLIPREF
AXIS=AXIS3
LEGEND=LEGEND1
;
PLOT / SUMVAR=ULyellow
TYPE=SUM
CLIPREF
AXIS=AXIS3
LEGEND=LEGEND1
;
PLOT / SUMVAR=LLred
TYPE=SUM
CLIPREF
AXIS=AXIS3
LEGEND=LEGEND1
;
PLOT / SUMVAR=ULred
TYPE=SUM
CLIPREF
AXIS=AXIS3
LEGEND=LEGEND1
;
BY Group;
ods pdf close;
/* -------------------------------------------------------------------
End of task code
------------------------------------------------------------------- */
/*RUN; QUIT; */
/*%_eg_conditional_dropds(WORK.SORTTempTableSorted);*/
/*TITLE; FOOTNOTE;*/
/*PATTERN1;*/
/*GOPTIONS RESET = SYMBOL;*/
%END;
%end;
ods pdf close;
%mend LOOP_OUTPUT;
%LOOP_OUTPUT;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.