Hi:
Without seeing your data and your calculations, it's hard to recommend an alternative solution. You can alter the erroneous percent at the break point (such as after zone), or, if you pre-summarize your data and then summarize your percent numbers, you can define your percent to be display or order (since you want to order on it) and then don't worry about it.
But now it seems to me that you have two issues. You have the percent being more than 100% issue and you still want/need to order on the computed variable.
Both of these items, taken together, indicate the need for a change of approach to me. There are ways to solve the percent issue using a COMPUTE block, because you can test the value at the break based on the _BREAK_ automatic variable. If you need to recalculate a summary total or blank it out completely, then you can do that. However, you still cannot use ORDER on a COMPUTED item, so something has to change. Switching to TABULATE might help with the percent issue but then you'd still have the ordering issue. So you may as well stick with PROC REPORT. PROC REPORT can make output datasets -- which will work to your advantage here.
If you make one pass through the data with PROC REPORT, and get the computed numbers in a dataset -- you've been able to use PROC REPORT for doing your computations. Still have the break issue, but you have now both presummarized your data and calculated your percent number. Then, with the pre-summarized data -- if you make a "copy" of the computed variable so you have it for ordering, you can order on the item that was computed in the first pass.
In the program below, PCTGRP is the computed percent for each group and PORD is just a "copy" of that variable. PCTGRP will be used in the FINAL report for summarizing and PORD will be used to order by DESCENDING value of the computed item. Note that for the jobcodes, the first pass report shows F and M rows in the same order for both jobcodes. But in the FINAL report, ordered by descending PORD within JOBCODE, for the AAA jobcode, the M row comes before the F row -- which is descending order.
This is some fake data that illustrates the concept, but depending on your data, you may or may not be able to use this logic out of the box. It really depends on your data. What you want to do (order on a computed item) is possible, and fixing the percent is possible.
cynthia
data work.fakedata;
keep jobcode gender salary ;
length jobcode $25 gender $1 Salary 8 ;
label gender= 'Employee Gender'
jobcode= 'Job Title'
Salary= 'Annual Salary';
format Salary DOLLAR12. ;
infile datalines delimiter=',' dsd;
input jobcode $ gender $ salary;
return;
datalines;
"BBBB BBB","M",32040
"BBBB BBB","F",30890
"AAA AAAA","M",30070
"AAA AAAA","F",28525
"BBBB BBB","M",32490
"AAA AAAA","F",29715
"AAA AAAA","M",29695
"AAA AAAA","M",30265
"AAA AAAA","M",28480
"AAA AAAA","M",29965
"AAA AAAA","F",30490
"AAA AAAA","F",29990
"AAA AAAA","M",36605
"BBBB BBB","F",30765
"AAA AAAA","F",30785
"BBBB BBB","M",30660
"AAA AAAA","M",28135
"AAA AAAA","M",28830
"AAA AAAA","M",28345
"AAA AAAA","F",28745
"AAA AAAA","M",28510
"AAA AAAA","F",28025
"BBBB BBB","F",32470
"AAA AAAA","F",30590
"AAA AAAA","M",29700
"AAA AAAA","F",29725
"BBBB BBB","M",31645
"AAA AAAA","M",30450
"AAA AAAA","M",30610
"AAA AAAA","F",29805
"AAA AAAA","F",28825
"BBBB BBB","F",30170
"AAA AAAA","M",28585
"BBBB BBB","F",31495
"AAA AAAA","M",29340
"AAA AAAA","M",28205
"AAA AAAA","M",30510
"BBBB BBB","M",32770
"BBBB BBB","F",32680
"BBBB BBB","F",31605
"BBBB BBB","F",32270
"BBBB BBB","M",30635
"BBBB BBB","M",31590
"AAA AAAA","F",28135
"AAA AAAA","M",30540
"BBBB BBB","M",32075
"AAA AAAA","M",28635
"AAA AAAA","M",30480
"AAA AAAA","F",29445
"BBBB BBB","M",30340
"AAA AAAA","M",30235
"BBBB BBB","M",31430
"AAA AAAA","F",28460
"AAA AAAA","F",28680
"AAA AAAA","M",28590
"BBBB BBB","F",39185
"AAA AAAA","M",29660
"AAA AAAA","F",28140
"AAA AAAA","F",30620
"BBBB BBB","M",31745
"AAA AAAA","M",30385
"AAA AAAA","M",28310
"AAA AAAA","M",28115
"AAA AAAA","F",28185
"AAA AAAA","M",28120
"AAA AAAA","F",29805
"AAA AAAA","M",28430
"AAA AAAA","M",29490
"AAA AAAA","M",28180
"AAA AAAA","F",30135
"BBBB BBB","M",31920
"AAA AAAA","F",37320
"AAA AAAA","F",30280
"AAA AAAA","M",29860
"AAA AAAA","F",28615
"AAA AAAA","M",30260
"AAA AAAA","M",28955
"AAA AAAA","M",29360
"AAA AAAA","M",29995
"AAA AAAA","M",29920
"AAA AAAA","M",29275
"AAA AAAA","F",29500
"AAA AAAA","M",28735
"AAA AAAA","F",30210
"AAA AAAA","F",29555
"AAA AAAA","F",30525
"AAA AAAA","F",30040
"BBBB BBB","M",30655
"AAA AAAA","M",30460
"AAA AAAA","M",29235
"AAA AAAA","M",29445
"BBBB BBB","F",32015
"BBBB BBB","M",32355
"BBBB BBB","M",30150
"AAA AAAA","M",30320
"AAA AAAA","M",28115
"AAA AAAA","M",28985
"AAA AAAA","F",28430
"AAA AAAA","M",29920
"AAA AAAA","M",28365
"AAA AAAA","F",31275
"BBBB BBB","F",31515
"AAA AAAA","M",28665
"BBBB BBB","M",31765
"AAA AAAA","F",34060
"BBBB BBB","F",30895
"BBBB BBB","M",30765
"AAA AAAA","M",29505
"AAA AAAA","F",28460
"BBBB BBB","F",30565
"AAA AAAA","F",29540
"BBBB BBB","F",40755
"AAA AAAA","F",30425
"BBBB BBB","F",31615
"AAA AAAA","F",29045
"AAA AAAA","F",28435
"BBBB BBB","F",31105
"AAA AAAA","M",29920
"AAA AAAA","M",28330
"AAA AAAA","F",28150
"AAA AAAA","M",29370
"AAA AAAA","M",29435
"AAA AAAA","F",29520
"AAA AAAA","M",28580
"AAA AAAA","M",29195
"BBBB BBB","F",30650
"AAA AAAA","F",30770
"AAA AAAA","M",29640
"AAA AAAA","M",30410
"AAA AAAA","M",28200
"BBBB BBB","M",31080
"AAA AAAA","M",29670
"BBBB BBB","F",30615
"BBBB BBB","F",32165
"BBBB BBB","F",32745
"BBBB BBB","F",32475
"BBBB BBB","F",30380
"AAA AAAA","M",30410
"BBBB BBB","M",32775
"BBBB BBB","M",31320
"BBBB BBB","F",31750
"BBBB BBB","F",32985
"BBBB BBB","M",32210
"BBBB BBB","M",31515
"AAA AAAA","M",28060
"BBBB BBB","M",31335
"AAA AAAA","F",29775
"AAA AAAA","M",28310
"AAA AAAA","F",29525
"AAA AAAA","M",28845
"AAA AAAA","F",29955
"AAA AAAA","M",29805
"AAA AAAA","M",30185
"AAA AAAA","F",28800
"AAA AAAA","M",29815
"BBBB BBB","F",30305
"AAA AAAA","F",28040
"BBBB BBB","F",31865
"AAA AAAA","F",29385
"AAA AAAA","M",28625
"AAA AAAA","M",28585
"AAA AAAA","F",30235
"AAA AAAA","M",28510
"BBBB BBB","M",32235
"BBBB BBB","M",32725
"AAA AAAA","F",29545
"BBBB BBB","F",31380
;
run;
ods listing close;
options nocenter noquotelenmax;
ods html file='c:\temp\grppct_with_comment.html'
style=sasweb;
proc report data=fakedata nowd out=work.pass1
style(summary)=Header;
title 'First Pass shows how PROC REPORT';
title2 'Calculated with Temporary Variables';
title3 'Only used breaks here to show PCTGRP calcs are wrong at _RBREAK_';
column jobcode
gender salary PctGrp pord
salary=psal;
define jobcode / group 'Job Title' f=$25.;
define gender / group 'Gender';
define salary / sum 'Salary' f=comma14.;
define pctgrp / computed 'PctGrp' f=percent9.2;
define pord / computed;
define psal / pctsum 'PSal' f=percent9.2;
break after jobcode / summarize;
rbreak after / summarize;
compute before jobcode;
grptot=salary.sum;
endcomp;
compute PctGrp;
PctGrp=salary.sum / grptot;
endcomp;
compute pord;
pord=pctgrp;
endcomp;
compute after jobcode /
style=Header;
line ' ';
endcomp;
compute after;
jobcode = 'Overall Report';
endcomp;
run;
proc print data=work.pass1;
where upcase(_break_) not in ('JOBCODE', '_RBREAK_');
title 'pass1: get computed PCTGRP and other vars';
title2 'ONLY need numbers, do not need any break lines';
format pctgrp psal pord best10.2;
run;
** sort by descending computed item;
proc sort data=work.pass1 out=work.final;
by jobcode descending pord;
where upcase(_break_) not in ('JOBCODE', '_RBREAK_');
run;
** create final report;
** after running once, then uncomment the NOPRINT for PORD;
** use ORDER=DATA for PORD to preserve DESCENDING order;
options missing = ' ';
proc report data=work.final nowd
style(summary)=Header;
title 'Final PROC REPORT ordered by DESCENDING PctGRP value (PORD)';
title2 'Use the NOPRINT option to hide PORD';
column jobcode pord gender salary pctgrp psal;
define jobcode / order 'Job Title' f=$25.
style(column)=Header;
define pord / order order=data /* noprint */
style(column)=Header ;
define gender / order 'Gender'
style(column)=Header;
define salary / sum 'Total Salary' f=comma14.;
define pctgrp / sum 'PctGrp' f=percent9.2;
define psal / sum 'PSal' f=percent9.2;
break after jobcode / summarize;
compute after jobcode;
line ' ';
endcomp;
rbreak after / summarize;
compute pctgrp;
if _break_ = '_RBREAK_' then do;
** could also just blank out the value;
** pctgrp.sum = .;
** this calc will make PCTGRP 100%;
pctgrp.sum = salary.sum/salary.sum;
end;
endcomp;
compute after;
** now adjust the summary label;
jobcode = 'Overall Total';
endcomp;
run;
ods _all_ close;
ods listing;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
1105 views
-
0 likes
-
-
