Hi, I want to get multiple tables per page, but each with a distinct title1(variable) and title2(month) - not just a title at the top of the page, but one for each table in the page.
PROBLEM: Using startpage=no I get one good title at the top of each page with the first output table, however, the following tables do not have a distinct title (nothing infact). I use a proc a sql statement for the outer loop, to cycle through each variable. Then, the inner loop cycles through time periods on each var by month. The print statement is defined in the outer loop because I want one pdf per variable that would contain multiple tables - one for each time period.
How would I modify the proc report statement so the I get a fresh title for each table in the inner loop. Or, am I just plain wrong about this approach all together? Thank you for any help.
%macro pdf(cp,yr,year);
proc sql noprint;select distinct mem3, mm into :cclist separated by ' '
from dat.xyz where varname1 in ("&cp") order by varname1, mm;
%let n_cclist= &sqlobs; quit;
%let j=1; %let cc=%scan(&cclist,&j,%str( ));
%do %while(%qscan(&cclist,&j,%STR( )) NE );
options missing='' nodate nonumber orientation=portrait ;
ODS LISTING ;
ods pdf file="H:\Shared\MDSEIMB\SFDS\SDS\Green Book\PDF\&cc._&yr..pdf" startpage=no;
proc sql noprint;select distinct Varname1, MM, SV, ESTIM, ESTIM2, Varname2, Month,
cem, cem2, cem3 into
:vxlist separated by ' ',
:mxlist separated by ' ',
:svlist separated by ' ',
:eslist separated by ' ',
:e2list separated by ' ',
:v2list separated by ' ',
:mhlist separated by ' ',
:crlist separated by ' ',
:c2list separated by ' ',
:c3list separated by ' '
from dat.xyz where varname1 in ("&cp") order by varname1, mm;
%let n_vxlist= &sqlobs; quit;
%let v=1; %let vx=%scan(&vxlist,&v.,%str( ));
%let m=1; %let mx=%scan(&mxlist,&m.,%str( ));
%let s=1; %let sv=%scan(&svlist,&s.,%str( ));
%let e=1; %let es=%scan(&eslist,&e.,%str( ));
%let f=1; %let e2=%scan(&e2list,&f.,%str( ));
%let w=1; %let v2=%scan(&v2list,&w.,%str( ));
%let h=1; %let mh=%scan(&mhlist,&h.,%str( ));
%let c=1; %let cr=%scan(&crlist,&c.,%str( ));
%let x=1; %let c2=%scan(&c2list,&x.,%str( ));
%let y=1; %let c3=%qscan(&c3list,&y.,%str( ));
%do %while(%scan(&vxlist,&v,%STR( )) NE );
proc report nowindows data=pdfd.&sv._&vx._&yr.&mx. SPLIT='*' ls=120
style(report)={rules=none frame=void cellspacing=0 cellpadding=3
borderwidth=0.3 borderleftwidth=0.3 borderrightwidth=0.3 }
style(column)={cellheight=0.175in font_face=Arial font_size=0.5 }
style(header)={font_face=Arial cellheight=0.40in font_size=0.5
bordertopcolor=black borderbottomcolor=black
borderbottomwidth=0.3 backgroundcolor=white };
title1 height=1 justify=center font=Arial &c2 ;/*Commodity name*/
title2 height=1 justify=center font=Arial "&mh &year. - J";
columns _ST2 tcv LSS NLS MFT LPC NPC LS_C NL_C MF_C RES LSP NLP;
define _ST2 / ' STX' left
style(column)={cellwidth=0.5in borderrightcolor=black borderrightwidth=0.3 indent=6};
define tcv / style(column)={cellwidth=0.5in} display format=comma4.1 'KCV' right ;
define LSS / style(column)={cellwidth=0.5in} display format=comma. 'XML' right;
define NLS / style(column)={cellwidth=0.5in} display format=comma. 'MOL' right;
define MFT / style(column)={cellwidth=0.7in} display format=comma14. 'MFX' right;
define LPC / style(column)={cellwidth=0.7in} display format=comma5.1 'PTEL' right;
define NPC / style(column)={cellwidth=0.7in} display format=comma4.1 'PTEN' right;
define LS_C / style(column)={cellwidth=0.5in} display format=comma4.1 'LCV' right;
define NL_C / style(column)={cellwidth=0.5in} display format=comma4.1 'NCV' right;
define MF_C / style(column)={cellwidth=0.5in} display format=comma4.1 'MCV' right;
define RES / style(column)={cellwidth=0.6in} display format=comma4.1 'RR' right;
define LSP / style(column)={cellwidth=0.6in} display format=comma. 'LUPs' right;
define NLP/ display format=comma. 'NUPs' right
style(column)={cellwidth=0.6in borderrightcolor=black};
compute MF_C; if TCV>0 and MF_C gt TCV then call define('MF_C','style',
'style={background=LIME}');
endcomp;
compute _ST2;if trim(_ST2) = 'U.S.' then call define(_row_,'style',
'style=[bordertopcolor=black borderbottomcolor=black
bordertopwidth=0.3 ]');
endcomp;
run;
%let v=%eval(&v+1); %let vx=%scan(&vxlist,&v.,%str( ));
%let m=%eval(&m+1); %let mx=%scan(&mxlist,&m.,%str( ));
%let s=%eval(&s+1); %let sv=%scan(&svlist,&s.,%str( ));
%let e=%eval(&e+1); %let es=%scan(&eslist,&e.,%str( ));
%let f=%eval(&f+1); %let e2=%scan(&e2list,&f.,%str( ));
%let w=%eval(&w+1); %let v2=%scan(&v2list,&w.,%str( ));
%let h=%eval(&h+1); %let mh=%scan(&mhlist,&h.,%str( ));
%let c=%eval(&c+1); %let cr=%scan(&crlist,&c.,%str( ));
%let x=%eval(&x+1); %let c2=%scan(&c2list,&x.,%str( ));
%let y=%eval(&y+1); %let c3=%qscan(&c3list,&y.,%str( ));
%end;
ODS _ALL_ CLOSE;
ODS LISTING;
%let j=%eval(&j+1); %let cc=%qscan(&cclist,&j.,%str( ));
%end;
%mend;
Hi:
The easiest way to insert a pseudo-title at the top of a procedure when you have STARTPAGE=NO is to use ODS TEXT. Since you did not post any data, I used SASHELP files and simplified the basic program to illustrate the ODS TEXT technique. The second approach would be to use a COMPUTE BEFORE _PAGE_ with PROC REPORT, but I recommend the ODS TEXT method first.
cynthia
ods path work.tmp(update) sasuser.templat(update) sashelp.tmplmst(read);
proc template;
define style styles.utext;
parent=styles.pearl;
class usertext from SystemTitle /
textalign=center;
end;
run;
ods pdf file='c:\temp\test1.pdf' startpage=no style=styles.utext;
title 'First Report';
proc report data=sashelp.class(obs=5);
run;
ods text='Second Report';
proc report data=sashelp.shoes(obs=5);
run;
ods pdf close;
title;
Hi:
The easiest way to insert a pseudo-title at the top of a procedure when you have STARTPAGE=NO is to use ODS TEXT. Since you did not post any data, I used SASHELP files and simplified the basic program to illustrate the ODS TEXT technique. The second approach would be to use a COMPUTE BEFORE _PAGE_ with PROC REPORT, but I recommend the ODS TEXT method first.
cynthia
ods path work.tmp(update) sasuser.templat(update) sashelp.tmplmst(read);
proc template;
define style styles.utext;
parent=styles.pearl;
class usertext from SystemTitle /
textalign=center;
end;
run;
ods pdf file='c:\temp\test1.pdf' startpage=no style=styles.utext;
title 'First Report';
proc report data=sashelp.class(obs=5);
run;
ods text='Second Report';
proc report data=sashelp.shoes(obs=5);
run;
ods pdf close;
title;
Thank you Cynthia. I appreciate your help.
I modified by moving the ODS PDF opening and closing statements. I also ADDED and additional ODS PDF Startpage= statement just before the PROC REPORT with a couple of text lines as you suggested.
%macro pdf5(c3,yr,year);
proc sql noprint;select distinct cr3 into
:cclist separated by ' '
from dat.xyz where cr3 in ("&c3");
%let n_cclist= &sqlobs; quit;
%put &cclist;
ODS LISTING close;
ods escapechar='^';
ods pdf file="H:\............\&c3._&yr..pdf" startpage=yes;
%let j=1; %let cc=%scan(&cclist,&j,%str( ));
%do %while(%scan(&cclist,&j,%STR( )) NE );
options missing='' nodate nonumber orientation=portrait ;
/*ODS LISTING close;*/
/*ods pdf file="H:\Shared\MDSEIMB\SFDS\SDS\Green Book\PDF\&cc._&yr..pdf" startpage=yes;*/
proc sql noprint;select distinct Varname1......., ......., etc......., Varname10 into
:vxlist separated by ' ',
(lists 1 to 10. etc)
:c3list separated by ' '
from dat.skinnyxy where crop3="&cc" order by varname1, mm;
%let n_vxlist= &sqlobs; quit;
%let v=1; %let vx=%scan(&vxlist,&v.,%str( ));
..
(List of counter vars 1-10 ect)
..
%let y=1; %let c3=%scan(&c3list,&y.,%str( ));
%do %while(%scan(&vxlist,&v,%STR( )) NE );
ods pdf startpage=no;
ods pdf text='^n^n';
ods pdf text="^S={outputwidth=100% just=c} &mh &c2 &year";
proc report nowindows data=pdfd.&sv._&vx._&yr.&mx. SPLIT='*' ls=120
style(report)={rules=none frame=void cellspacing=0 cellpadding=3
borderwidth=0.3 borderleftwidth=0.3 borderrightwidth=0.3 }
style(column)={cellheight=0.175in font_face=Arial font_size=0.5 }
style(header)={font_face=Arial cellheight=0.40in font_size=0.5
bordertopcolor=black borderbottomcolor=black
borderbottomwidth=0.3 backgroundcolor=white };
title1 height=12pt justify=center font=Arial bold &c2;/*Commodity name*/
/*title2 height=1 justify=center font=Arial " &year. J";*/
columns _var1-_varxx (list the variables in the table);
define _var1 / ' State' left
style(column)={cellwidth=0.5in borderrightcolor=black borderrightwidth=0.3 indent=6};
define var2 / style(column)={cellwidth=0.5in} display format=comma4.1 'Var description' right ;
etc
etc
(Define each variable name as you want it listed in the table.)
etc
define varxx / display format=comma. 'varxx description' right
style(column)={cellwidth=0.6in borderrightcolor=black};
run;
%let v=%eval(&v+1); %let vx=%scan(&vxlist,&v.,%str( ));
etc
(List if counting variables)
etc
%let y=%eval(&y+1); %let c3=%scan(&c3list,&y.,%str( ));
%end;
%let j=%eval(&j+1); %let cc=%qscan(&cclist,&j.,%str( ));
%end;
ODS _ALL_ CLOSE;
ODS LISTING;
%mend;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: