BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Q1983
Lapis Lazuli | Level 10

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

2 REPLIES 2
Haris
Lapis Lazuli | Level 10

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 28839 views
  • 0 likes
  • 3 in conversation