DATA Step, Macro, Functions and more

Sorting descending grand total in proc tabulate

Reply
Frequent Contributor
Posts: 131

Sorting descending grand total in proc tabulate

proc sql;

create table reg_1_summary as

select sale_rep_id_cd,acct_exec_nm,THRD_PARTY_ORIG_NM,THRD_PARTY_ORIG_CD,count(ln_nbr) as ln

from reg_2

group by sale_rep_id_cd,acct_exec_nm,THRD_PARTY_ORIG_NM,THRD_PARTY_ORIG_CD

;quit;

 

This produces sample data with the above decalred variables.  I created a proc tabulate for the datastep

Proc tabulate data=reg_2_summary order= data format=10. S=[cellwidth=150];

Class sale_rep_id_cd acct_exec_nm THRD_PARTY_ORIG_NM THRD_PARTY_ORIG_CD /*THRD_PARTY_ORIG_NM Platform*/ ;

Var ln ;

Table sale_rep_id_cd=' ' all={label='Grand Total' S=[background = lightblue cellwidth=160]} *[STYLE=[Font_Weight=BOLD]],

acct_exec_nm='Acct Mgr '*ln=' '*sum=' '/*Platform='Platform '*ln=' '*sum=' '*/

all={label='Grand Total' S=[background = lightblue]} *[STYLE=[Font_Weight=BOLD]] *ln=' '*sum=' ' / box='Sales Rep ID';

TITLE 'Post Deploy Summary';

footnote1 &dekeyp;

run;

 

I am trying to sort in descending order by the Grand Total.  

Respected Advisor
Posts: 4,651

Re: Sorting descending grand total in proc tabulate

To get ordering on both line and column Totals, you probably have to add all missing cell values and generate the order yourself. Something like the following:

 

proc sql;
create table temp as 
select a.repName, b.mgrName, c.ln 
from
    (select repName, sum(ln) as repTotal 
        from have group by repName) as a cross join
    (select mgrName, sum(ln) as mgrTotal 
        from have group by mgrName) as b left join 
    have as c 
        on a.repName=c.repName and b.mgrName=c.mgrName
order by a.repTotal desc, b.mgrTotal desc;
quit;

proc tabulate data=temp order=data format=10.;
class repName mgrName;
var ln;
table repName="Rep. Name" all, (mgrName="Mgr. Name" all)*ln=""*sum="";
run; 
PG
Ask a Question
Discussion stats
  • 1 reply
  • 291 views
  • 0 likes
  • 2 in conversation