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.
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;
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 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.