I've got this to work as intended. I'll answer your questions first then explain how I got it working. This report is part of a bigger macro program and yes, PDF, is my ODS destination - sorry, I thought that was obvious from the subject line. I used the double loop for two reasons: first was for getting the page numbers how I want them - the "bypageno" does not work correctly (though I haven't followed up with Technical Support to see about any possible fixes) and second, originally the report created separate reports by Regname. There are also advantages for the titles and labels. This report is output as one report so I simplified the looping, I removed the first loop by regname as it was really un-necessary for this particular program.
The ORDER_RET_xxx and VAL_RET_xxxx are count variables - in this instance they are either a 1 or 0 depending on if there were orders or validations by that retailer. So I'm using these for the total counts. They do occur on the column statement to the left of where I'm using them in the calculation, so there are no issues there as far as using them in the calculations.
Sorry I didn't include the options statement, it is indeed a landscape report. When I learned Proc Report, I wasn't really using any other destination but listing output so I guess I learned it using the "width=" in the define statements. In order to streamline future reports to the PDF destination, I will stop including them. I have found that using style={cellwidth=xxx} does work to control width in PDF when it is necessary.
Now for the fix: the variables FirstOrd_txt_&Game1, etc. FirstVal_txt_&Game1. etc. are text variables I created from date fields when I was trying different methods. I re-verted back to using the date variable version. I then did my calculations as temporary variables and then made them equal the date variables in the compute after statement. Essentially, I removed the "trim(put(..." which was making them text.
Here is the updated and complete code:
[pre]
%macro Games;
options nodate number pageno=1 orientation=landscape missing=' ' leftmargin='.1in' rightmargin='.1in'
topmargin='.35in' bottommargin='.35in';
ODS _ALL_ close; ODS escapechar='~';
ODS PDF (id=req) file="X:\XXXX\XXXXX\xxxxxx\Game Launch &LaunchDate Update.pdf" uniform style=styles.MediumBy;
/*Game Launch Summary*/
ods proclabel "Game Launch Summary";
proc report data=Launch_Sum_Reg_Dist_Route nowd headline headskip list split='*'
style(report)=[rules=ALL frame=BOX]
style(header)={font_face=Arial fontsize=9pt font_weight=bold background=CXFFFF99 foreground=CX003399}
style(column)={font_face=Arial fontsize=8pt cellheight=42 vjust=m}
style(summary)={font_face=Arial fontsize=9pt font_weight=bold just=r vjust=m cellheight=44} contents="";
column table_label_remove ('~{style [cellheight=44 vjust=m fontsize=10pt] }' regsort regname dsmname lsrname Current_RET )
("~{style [cellheight=44 vjust=m fontsize=10pt]&PP1 - &Game1 - &Gname1}" Order_Ret_&Game1 Val_Ret_&Game1 Selling_&Game1 Selling_pct1)
("~{style [cellheight=44 vjust=m fontsize=10pt]&PP2 - &Game2 - &Gname2}" Order_Ret_&Game2 Val_Ret_&Game2 Selling_&Game2 Selling_pct2)
("~{style [cellheight=44 vjust=m fontsize=10pt]&PP3 - &Game3 - &Gname3}" Order_Ret_&Game3 Val_Ret_&Game3 Selling_&Game3 Selling_pct3)
("~{style [cellheight=44 vjust=m fontsize=10pt]Selling Any Game from Launch}" Any_Order_Ret Any_Order_pct Any_Val_Ret Any_Val_pct Any_Selling Any_pct);
define table_label_remove / order noprint;
define regsort / order "Region Sort" f=6. noprint;
define regname / order "Region" f=$20. noprint;
define dsmname / order "District" f=$20. noprint;
define lsrname / order "LSR" f=$22. style={cellwidth=180};
define Current_Ret / "Current*Retailers" width=9 f=comma9.;
define Order_Ret_&Game1 / "Orders" f=comma9. style={cellwidth=100};
define Val_Ret_&Game1 / "Cashes" f=comma9. noprint style={cellwidth=90};
define Selling_&Game1 / "Selling" f=comma9. style={cellwidth=100};
define Selling_pct1 / computed "Pct*Selling" f=percent9.1 style={cellwidth=90};
compute Selling_pct1; Selling_pct1=Selling_&Game1..sum/Current_Ret.sum; endcomp;
define Order_Ret_&Game2 / "Orders" f=comma9. style={cellwidth=100};
define Val_Ret_&Game2 / "Cashes" f=comma9. noprint style={cellwidth=90};
define Selling_&Game2 / "Selling" f=comma9. style={cellwidth=100};
define Selling_pct2 / computed "Pct*Selling" f=percent9.1 style={cellwidth=90};
compute Selling_pct2; Selling_pct2=Selling_&Game2..sum/Current_Ret.sum; endcomp;
define Order_Ret_&Game3 / "Orders" f=comma9. style={cellwidth=100};
define Val_Ret_&Game3 / "Cashes" f=comma9. noprint style={cellwidth=90};
define Selling_&Game3 / "Selling" f=comma9. style={cellwidth=100};
define Selling_pct3 / computed "Pct*Selling" f=percent9.1 style={cellwidth=90};
compute Selling_pct3; Selling_pct3=Selling_&Game3..sum/Current_Ret.sum; endcomp;
define Any_Order_Ret / "Orders" f=comma9. style={cellwidth=100};
define Any_Order_pct / computed "Pct*Orders" f=percent9.1 style={cellwidth=90};
compute Any_Order_pct; Any_Order_pct=Any_Order_Ret.sum/Current_Ret.sum; endcomp;
define Any_Val_Ret / "Cashes" f=comma9. noprint /*style={cellwidth=90}*/;
define Any_Val_pct / computed "Pct*Cashes" width=9 f=percent9.1 noprint style={cellwidth=90};
compute Any_Val_pct; Any_Val_pct=Any_Val_Ret.sum/Current_Ret.sum; endcomp;
define Any_Selling / "Selling" f=comma9. style={cellwidth=100};
define Any_pct / computed "Pct*Selling" f=percent9.1 style={cellwidth=90};
compute Any_pct; Any_pct=Any_Selling.sum/Current_Ret.sum; endcomp;
break before table_label_remove / contents='' page;
break after dsmname / summarize style={background=CXDDDDDD};
compute after dsmname;
lsrname="DSM "||trim(left(substr(dsmname,1,4)))||" Totals";
endcomp;
break after regname / summarize style={background=CXCAE1FF};
compute after regname;
lsrname=trim(left(propcase(regname)))||" Totals:";
endcomp;
rbreak after / summarize skip style={background=CXFFFF33 cellheight=44 fontsize=8.5pt};
compute after;
lsrname='STATEWIDE:';
endcomp;
title1 font=arial italic bold height=16pt "XXXXXXXXX";
title2 font=arial italic height=14pt "&LaunchDate Game Launch Orders and Validations";
title3 font=arial italic height=14pt "Statewide Retailer Summary through &RunThrough_Long";
title4 font=arial italic height=6pt " ";
run; title; footnote;
data _NULL_; retain cnt 0; set Final_LSR_Orders_Validations; by lsrname;
if first.lsrname then do; cnt+1;
call symput('LSR'||trim(put(cnt, 2. -L)), trim(lsrname));
end; call symput('Ltot', trim(put(cnt, 3. -L))); run;
%do j=1 %to &Ltot;
options pageno=1;
ods proclabel "&&LSR&j";
proc report data=Final_LSR_Orders_Validations nowd headline headskip list split='*' out=chkbreak
style(report)=[rules=ALL frame=BOX]
style(header)={font_face=Arial fontsize=8pt font_weight=bold background=CXFFFF99 foreground=CX003399}
style(column)={font_face=Arial fontsize=7pt cellheight=40 vjust=m}
style(summary)={font_face=Arial fontsize=8pt font_weight=bold just=c vjust=m cellheight=44} contents="";
column table_label_remove ('~{style [cellheight=44 vjust=m fontsize=10pt] }' /*regname*/ lsrname)
('~{style [cellheight=44 vjust=m fontsize=10pt]Retailer Info}' RID Chain bname street city iss_facings Current_RET)
(' ' Order_Ret_&Game1 Order_Ret_&Game2 Order_Ret_&Game3 Val_Ret_&Game1 Val_Ret_&Game2 Val_Ret_&Game3)
("~{style [cellheight=44 vjust=m fontsize=10pt]&PP1 - &Game1 - &Gname1}" FirstOrd_&Game1 FirstVal_&Game1 LastVal_&Game1)
("~{style [cellheight=44 vjust=m fontsize=10pt]&PP2 - &Game2 - &Gname2}" FirstOrd_&Game2 FirstVal_&Game2 LastVal_&Game2)
("~{style [cellheight=44 vjust=m fontsize=10pt]&PP3 - &Game3 - &Gname3}" FirstOrd_&Game3 FirstVal_&Game3 LastVal_&Game3);
define table_label_remove / group noprint;
/*define regname / group "Region" f=$20. noprint;*/
define lsrname / group "LSR" f=$22. noprint;
define RID / order "RID" f=$6. center;
define chain / "Chain" f=$6. center;
define bname / "Store Name" f=$24. ;
define street / "Address" f=$18. ;
define city / "City" f=$15. ;
compute city; if _BREAK_="_RBREAK_" then call define(_COL_, "style", "style={just=r}"); endcomp;
define iss_facings / "ISS Facings" width=6 f=6. display center;
define Current_Ret / "Current*Retailers" width=9 f=comma9. noprint;
define Order_Ret_&Game1 / f=8. sum noprint;
define Order_Ret_&Game2 / f=8. sum noprint;
define Order_Ret_&Game3 / f=8. sum noprint;
define Val_Ret_&Game1 / f=8. sum noprint;
define Val_Ret_&Game2 / f=8. sum noprint;
define Val_Ret_&Game3 / f=8. sum noprint;
define FirstOrd_&Game1 / "First*Order" f=mmddyy8. center sum ;
compute FirstOrd_&Game1; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define FirstVal_&Game1 / "First*Validation" f=mmddyy8. center sum ;
compute FirstVal_&Game1; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define LastVal_&Game1 / "Last*Validation" f=mmddyy8. center sum ;
compute LastVal_&Game1; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "percent8.1"); endcomp;
define FirstOrd_&Game2 / "First*Order" f=mmddyy8. center sum;
compute FirstOrd_&Game2; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define FirstVal_&Game2 / "First*Validation" f=mmddyy8. center sum;
compute FirstVal_&Game2; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define LastVal_&Game2 / "Last*Validation" f=mmddyy8. center sum;
compute LastVal_&Game2; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "percent8.1"); endcomp;
define FirstOrd_&Game3 / "First*Order" f=mmddyy8. center sum;
compute FirstOrd_&Game3; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define FirstVal_&Game3 / "First*Validation" f=mmddyy8. center sum;
compute FirstVal_&Game3; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define LastVal_&Game3 / "Last*Validation" f=mmddyy8. center sum;
compute LastVal_&Game3; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "percent8.1"); endcomp;
break before table_label_remove / contents='' page;
compute before; totrets=current_ret.sum;
totorder1=order_ret_&game1..sum; totval1=val_ret_&game1..sum; pctval1=val_ret_&game1..sum/current_ret.sum;
totorder2=order_ret_&game2..sum; totval2=val_ret_&game2..sum; pctval2=val_ret_&game2..sum/current_ret.sum;
totorder3=order_ret_&game3..sum; totval3=val_ret_&game3..sum; pctval3=val_ret_&game3..sum/current_ret.sum;
endcomp;
rbreak after / summarize style={background=CXCAE1FF};
compute after; bname=trim(put(current_ret.sum, 6. -L))||" Retailers"; city='Total: ';
FirstOrd_&Game1..sum=totorder1; FirstVal_&Game1..sum=totval1; LastVal_&Game1..sum=pctval1;
FirstOrd_&Game2..sum=totorder2; FirstVal_&Game2..sum=totval2; LastVal_&Game2..sum=pctval2;
FirstOrd_&Game3..sum=totorder3; FirstVal_&Game3..sum=totval3; LastVal_&Game3..sum=pctval3;
endcomp;
where lsrname="&&LSR&j";
title1 font=arial italic bold height=16pt "XXXXXXXXX";
title2 font=arial italic height=14pt "&LaunchDate Game Launch Orders and Validations through &RunThrough_Long";
title3 font=arial italic height=14pt "&&LSR&j";
title4 font=arial italic height=6pt " ";
run; title; footnote;
%end;
ODS PDF (id=req) close;
ODS listing;
%mend Games;
%Games
[/pre]
... View more