RBreak - some counts not showing up on PDF

[Ross]

Hello-

I'm trying to put various counts into some text variables in the summary line. I've used this technique before but for some reason it won't display in this PDF. The counts show up on the _RBREAK_ row in the out= dataset (chkbreak). The formats also change properly on the PDF (from the call define statements) - but they just show 0 instead of the counts. I've gone back and forth creating them as temporary variables using compute before and then using those but the results are the same (show as 0). The text variables have a width of 8 and the counts get no higher than 3 digits. There are also no errors in the log. For what its worth, the count for current_ret.sum that is "put" into the bname variable does show up properly. Am I missing something here? (I left out the ODS "sandwich" portion of the code to keep it a little simpler) Any help would be appreciated. Thanks.

[pre]
data _NULL_; retain cnt 0; set Final_Orders_Validations; by regsort regname dsmname lsrname;
if first.regname then do; cnt+1;
call symput('REG'||trim(put(cnt, 2. -L)), trim(regname));
end; call symput('Rtot', trim(put(cnt, 3. -L))); run;
%do i=1 %to &Rtot;

data _NULL_; retain cnt 0; set Final_LSR_Orders_Validations; by regsort regname lsrname;
where regname="&&REG&i";
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]&Game1 - &Gname1}" FirstOrd_txt_&Game1 FirstVal_txt_&Game1 LastVal_txt_&Game1)
("~{style [cellheight=44 vjust=m fontsize=10pt]&Game2 - &Gname2}" FirstOrd_txt_&Game2 FirstVal_txt_&Game2 LastVal_txt_&Game2)
("~{style [cellheight=44 vjust=m fontsize=10pt]&Game3 - &Gname3}" FirstOrd_txt_&Game3 FirstVal_txt_&Game3 LastVal_txt_&Game3);

define table_label_remove / group noprint;
define regname / group "Region" width=20 f=$20. noprint;
define lsrname / group "LSR" width=22 f=$22. noprint;
define RID / order "RID" width=6 f=$6. center;
define chain / "Chain" width=6 f=$6. center;
define bname / "Store Name" width=24 f=$24. ;
define street / "Address" width=18 f=$18. ;
define city / "City" width=15 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 / width=8 f=8. sum noprint;
define Order_Ret_&Game2 / width=8 f=8. sum noprint;
define Order_Ret_&Game3 / width=8 f=8. sum noprint;

define Val_Ret_&Game1 / width=8 f=8. sum noprint;
define Val_Ret_&Game2 / width=8 f=8. sum noprint;
define Val_Ret_&Game3 / width=8 f=8. sum noprint;

define FirstOrd_txt_&Game1 / "First*Order" width=8 f=$8. center ;
compute FirstOrd_txt_&Game1; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define FirstVal_txt_&Game1 / "First*Validation" width=8 f=$8. center ;
compute FirstVal_txt_&Game1; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define LastVal_txt_&Game1 / "Last*Validation" width=8 f=$8. center ;
compute LastVal_txt_&Game1; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "percent6."); endcomp;
define FirstOrd_txt_&Game2 / "First*Order" width=8 f=$8. center;
compute FirstOrd_txt_&Game2; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define FirstVal_txt_&Game2 / "First*Validation" width=8 f=$8. center;
compute FirstVal_txt_&Game2; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define LastVal_txt_&Game2 / "Last*Validation" width=8 f=$8. center;
compute LastVal_txt_&Game2; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "percent6."); endcomp;
define FirstOrd_txt_&Game3 / "First*Order" width=8 f=$8. center;
compute FirstOrd_txt_&Game3; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define FirstVal_txt_&Game3 / "First*Validation" width=8 f=$8. center;
compute FirstVal_txt_&Game3; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "comma6."); endcomp;
define LastVal_txt_&Game3 / "Last*Validation" width=8 f=$8. center;
compute LastVal_txt_&Game3; if _BREAK_="_RBREAK_" then call define(_COL_, "format", "percent6."); endcomp;

break before table_label_remove / contents='' page;
/*compute before; totrets=current_ret.sum;
totorder1=; totval1=; pctval1=;
totorder2=; totval2=; pctval2=;
totorder3=; totval3=; pctval3=;
endcomp; */

rbreak after / summarize style={background=CXCAE1FF};
compute after; bname=trim(put(current_ret.sum, 6. -L))||" Retailers"; city='Total: ';
FirstOrd_txt_&Game1=trim(put(order_ret_&game1..sum, 6. -L)); FirstVal_txt_&Game1=trim(put(val_ret_&game1..sum, 6. -L)); LastVal_txt_&Game1=trim(put(val_ret_&game1..sum/current_ret.sum, percent8.1 -L));
FirstOrd_txt_&Game2=trim(put(order_ret_&game2..sum, 6. -L)); FirstVal_txt_&Game2=trim(put(val_ret_&game2..sum, 6. -L)); LastVal_txt_&Game2=trim(put(val_ret_&game2..sum/current_ret.sum, percent8.1 -L));
FirstOrd_txt_&Game3=trim(put(order_ret_&game3..sum, 6. -L)); FirstVal_txt_&Game3=trim(put(val_ret_&game3..sum, 6. -L)); LastVal_txt_&Game3=trim(put(val_ret_&game3..sum/current_ret.sum, percent8.1 -L));
endcomp;

where regname="&&REG&i" and lsrname="&&LSR&j";

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 "&&REG&i Region";
title4 font=arial italic height=14pt "&&LSR&j";
title5 font=arial italic height=8pt " ";
run; title; footnote;
%end; %end;
[/pre]

Message was edited by: Ross

[Cynthia_sas]

Hi:
Not only have you left off the ODS "sandwich" -- but it seems you have a macro %DO loop and you have left off the macro invocation as well.

Just out of curiousity, what is your destination of interest?? Based on your posting title, it seems like it is ODS PDF. It also looks like you are simulating BY group processing through the use of your %DO loop -- something I don't entirely understand -- you are generating a report for every unique combination of REGNAME and LSRNAME???? And it looks like the only reason you are using macro variables for REGNAME and LSRNAME is in order to use the REGNAME and LSRNAME in the title statements. Since this is something you could do with BY group processing and the #BYVAL special variables, I'm still not clear on why you need a macro program.

Also, I find your code a bit squished together and hard to read and without seeing the data, hard to understand. The COMPUTE blocks interspersed and squished together with the DEFINE statements aren't so bad, but the use of the ORDER_RET_xxx and VAL_RET_xxx variables doesn't make sense to me. Can you replicate your issue if you simplify the looping and perhaps use one of the SASHELP datasets???

Remember that PROC REPORT processes the report report row from Left to Right. When you are using items in a COMPUTE block, if the items appear on a COLUMN statement, then they must occur BEFORE a COMPUTED variable in order to be used in the calculation of a COMPUTED variable. So, for example:
[pre]
column name fakevar age height;
compute fakevar;
fakevar = height.sum / age.sum;
endcomp;
[/pre]

would be WRONG referencing because if you plan to use AGE and HEIGHT in the calculation of FAKEVAR, then they must be placed on each report row prior to the placement of FAKEVAR. So this would be the correct referencing:
[pre]
column name age height fakevar ;
compute fakevar;
fakevar = height.sum / age.sum;
endcomp;
[/pre]

Also FYI, some PROC REPORT options (such as WIDTH= were originally designed for the LISTING destination and have no impact on PDF, RTF and HTML destinations). You don't show your system options, but I am assuming that orientation is being set to LANDSCAPE?? Otherwise, the report looks very wide for a portrait mode report. Do you get any error messages in the SAS log when you run this program??? If so, what are they??

The program shown below has a simple example of using #BYVAL for two countries and two products from SASHELP.PRDSALE. It also has a calculated variable and a LINE statement that uses the summary of the calculated variable at the break point.

cynthia
[pre]
proc sort data=sashelp.prdsale out=prdsale;
by country product region division month;
where country in ('CANADA', 'GERMANY') and
product in ('DESK', 'CHAIR') and
year = 1994 and quarter=1;
run;

ods listing close;
options orientation=landscape nobyline nodate number pageno=1;
ods pdf file='c:\temp\use_byinfo.pdf';

proc report data=prdsale nowd;
by country product;
title1 font=helvetica italic bold height=16pt "XXXXXXXXX";
title2 font=helvetica italic height=14pt "&sysdate9 Game Launch Orders and Validations";
title3 font=helvetica italic height=14pt '#byval1 Country';
title4 font=helvetica italic height=14pt '#byval2 Product';
title5 font=helvetica italic height=8pt " ";
column country product region division month actual predict calcvar;
define country / group noprint;
define product / group noprint;
define region / group order=internal;
define division / group order=internal;
define month / group f=monyy5. order=internal;
define actual / sum f=dollar16.0;
define predict / sum f=dollar16.0;
define calcvar / computed f=dollar14.0 'Calc Diff';
break after region / summarize
style={background=CXCAE1FF};
break before region / contents='' page;
compute calcvar;
calcvar = actual.sum-predict.sum ;
endcomp;
compute after region;
line 'The overall difference between Actual and Predicted Sales is: ' calcvar dollar14.0;
endcomp;
run;

ods _all_ close;
options byline;
title; footnote;
ods listing;
[pre]

[Ross]

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]