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;
... View more