Sort order by descending total and variable in alpha order

Q1983 — Mon, 07 Dec 2015 15:53:01 GMT

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

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

Haris — Mon, 07 Dec 2015 17:28:54 GMT

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;

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

PGStats — Mon, 07 Dec 2015 18:59:45 GMT

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;

topic Sort order by descending total and variable in alpha order in SAS Programming

Sort order by descending total and variable in alpha order

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

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