## Sort order by descending total and variable in alpha order

Regular Contributor
Posts: 179

# Sort order by descending total and variable in alpha order

proc sql;

create table post_deploy_2 as

select SALE_REP_NM,acct_exec_nm,THRD_PARTY_ORIG_NM,THRD_PARTY_ORIG_CD,count(ln_nbr) as Total_Count label='Total'

from post_deploy_1

group by SALE_REP_NM,acct_exec_nm,THRD_PARTY_ORIG_NM,THRD_PARTY_ORIG_CD

;quit;

Sample data

 SALE_REP_NM ACCT_EXEC_NM THRD_PARTY_ORIG_NM THRD_PARTY_ORIG_CD Total_Count T_M A FINANCIAL MORTGAGE CO KX157 2 T_M R FINANCIAL SERVICES, INC. KX172 1 T_M SL CAPITAL, LLC NVT20 3 T_M SN FIRST BANK CWW81 4 Amber Doe P Smith APD FUNDING CORP NVL84 2 Amber Doe P Smith BR SAVINGS BANK NTK65 2 Amber Doe P Smith CFUND MORTGAGE CORP NVQ59 3 Amber Doe P Smith CTHM, LLC NJL71 1 Jane Doe P Smith FIRST FED BK N491A 5 Jane Doe P Smith KE MORTG NGW93 1 Jane Doe T Andrews PT COMMUNITY BANK NUS07 3 Jane Doe T Andrews SY SECURITIES, INC. NTK73 3 D Nelson T Andrews ACNB BANK N880X 6 D Nelson T Andrews AME MORTGAGE COMPANY EFP24 1 D Nelson T Andrews FI SOUTH BANK NC K724B 1 D Nelson T Andrews GATBANK MORTGAGE, INC KUQ56 1

proc sql;

create table post_deploy_3 as

select * from post_deploy_2

group by SALE_REP_NM

order by Total_Count desc

;quit;

My objective is to show the SALE_REP_NM one time and all of their acct_exec_nm, THRD_PARTY_ORIG_CD and Total_Count in descending order

Something like this

 SALE_REP_NM ACCT_EXEC_NM THRD_PARTY_ORIG_NM THRD_PARTY_ORIG_CD Total_Count T_M SN Capital Bank CWW81 4 T_M SL Capital Bank NVT20 3 T_M A Financial mortgate KX157 2 T_M R Financial KX172 1 Jane Doe P Smith FIRST FED BK N491A 5 Amber Doe P Smith CFUND MORTGAGE CORP NVQ59 3 Amber Doe P Smith APD FUNDING CORP NVL84 2 Amber Doe P Smith BR SAVINGS BANK NTK65 2 Amber Doe P Smith CTHM, LLC NJL71 1

I am attempting to sort in total_count descending order and by sales rep in alpha order.  The present code tends to repeat the sale_rep_nm in different parts of the report.  I am attempting a “rollup” type report with the sale_rep_nm showing just once

Frequent Contributor
Posts: 117

## Re: Sort order by descending total and variable in alpha order

You list the ordering sequence the way you want it in the ORDER BY statement.  DESC option applies to each variable one at a time.  I think I am missing something, though.

proc sql;

create table post_deploy_3 as

select * from post_deploy_2

group by SALE_REP_NM

order by SALE_REP_NM desc, acct_exec_nm desc, THRD_PARTY_ORIG_CD desc, Total_Count desc

;

quit;

Posts: 5,543

## Re: Sort order by descending total and variable in alpha order

So what you want is:

``````proc sql;
create table post_deploy_2 as
select
SALE_REP_NM,
acct_exec_nm,
THRD_PARTY_ORIG_NM,
THRD_PARTY_ORIG_CD,
count(ln_nbr) as Total_Count label='Total'
from post_deploy_1
group by SALE_REP_NM, acct_exec_nm, THRD_PARTY_ORIG_NM, THRD_PARTY_ORIG_CD
order by SALE_REP_NM, Total_count DESC;
quit;``````
PG
Discussion stats
• 2 replies
• 7578 views
• 0 likes
• 3 in conversation