BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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.  

1 REPLY 1
PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1167 views
  • 0 likes
  • 2 in conversation