Sort order by descending total and variable in alpha order

[Q1983]

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

[Haris]

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;

[PGStats · Accepted solution]

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;