DATA Step, Macro, Functions and more

Sort order by descending total and variable in alpha order

Reply
Frequent Contributor
Posts: 131

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: 102

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;

Respected Advisor
Posts: 4,649

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
Ask a Question
Discussion stats
  • 2 replies
  • 3085 views
  • 0 likes
  • 3 in conversation