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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.