Sorry, attached is a sample of the output before I blank out some of the data: I want to insert blanks when the Sum of incumbents for any breakout of the data is less than 5. But I want the calculations for the "ALL" summary to remain the same because they are accurate. Here is what happens when I run a report of this table using Summarize in the report code: You can see that the "Summarize" function could not produce the correct "Average" or Percentiles because the detailed data table was not used. Not shown in this example is how I blanked the salary data when the number of incumbents reported in a peer group was less than 5. Here is the code I used to build the initial tabulation and then blank the variables: PROC TABULATE Data=WORK.SABA_DATA CLASSDATA=WORK.SABA_DATA out=Work.OUT1;
options nodate pageno=1 linesize=64
pagesize=40;
Class JobCode JobTitle Peer_Code / missing ;
var Numincum Salary Bonus Total_Cash;
table JobCode*JobTitle*(Peer_Code ALL), (Sum*Numincum="Incumbents"*f=6.) (Mean="Average"*Salary*f=COMMA8.)
(p25="25th Percentile"*Salary*f=COMMA8.)(Median*Salary*f=COMMA8.) (p75="75th Percentile"*Salary*f=COMMA8.)
(N="Count"*Bonus) (Mean="Average"*Bonus*f=COMMA8.) (Mean="Average Total Cash"*Total_Cash*f=COMMA8.) ;
Run;
Data Work.OUT1;
Set Work.out1;
If Numincum_Sum<5 Then
DO;
Salary_Mean="";
Salary_P25="";
Salary_Median=" ";
Salary_P75=" ";
Bonus_Mean=" ";
Total_Cash_Mean=" ";
End;
Run; The final report after blanking the variables where necessary was created by the following code: Proc Report Data=Work.Out1;
define JobCode / order ;
define JobTitle / order ;
define Peer_Code / order order=formatted ;
Column JobCode JobTitle Peer_Code Numincum_Sum Salary_Mean Salary_P25 Salary_Median Salary_P75 Bonus_N Bonus_Mean Total_Cash_Mean;
define Numincum_Sum / format=COMMA8. "Incumbents" '';
define Salary_Mean / format=COMMA9. 'Average Salary' Mean;
define Salary_P25 / format=COMMA9. '25th Percentile Salary' P25;
define Salary_Median / format=COMMA9. 'Median Salary' Median;
define Salary_P75 / format=COMMA9. '75th Percentile Salary' P75;
define Bonus_N / format=COMMA8. 'Number of Bonuses';
define Bonus_Mean / format=COMMA9. 'Average Bonus' Mean;
define Total_Cash_Mean / format=COMMA9. 'Average Total Cash' Mean;
Break AFTER JobCode / ul Ol summarize suppress skip;
compute after;
line 'ALL' Format=Comma9.;
EndComp;
Run; So, I thought about adding code to the Proc Report to calculate the weighted average salary, but that will not help with the Percentiles. It would be great if I could blank variables as needed in the Proc Tabulate for a number of reason. Thanks for your help.
... View more