DATA Step, Macro, Functions and more

Aggregate totals Duplicates in proc sql

Reply
Frequent Contributor
Posts: 141

Aggregate totals Duplicates in proc sql

proc sql;

create table exc_cnt as

select prog_ctgy,CODEDESC,count(ln_cnt) as cnt_cd,

           sum(GROSS_LN_AMT) as gr_amt format=dollar12.2 ,

           GROSS_LN_AMT/sum(GROSS_LN_AMT)*100 as amt_pct

           from loan7

where CODEDESC ne ''

group by prog_ctgy ,CODEDESC having cnt_cd

;quit;

Output issue with aggregates  how would i structure the code to avoid the duplicates

prog_ctgy

CODEDESC

cnt_cd

gr_amt

amt_pct

Doctor

101 - Appraisal / Property

12

$7,640,316.00

7.866166792

Doctor

101 - Appraisal / Property

12

$7,640,316.00

11.79925019

Doctor

101 - Appraisal / Property

12

$7,640,316.00

11.77830865

Doctor

101 - Appraisal / Property

12

$7,640,316.00

12.89724142

Doctor

101 - Appraisal / Property

12

$7,640,316.00

6.904164697

Doctor

101 - Appraisal / Property

12

$7,640,316.00

5.456580592

Doctor

101 - Appraisal / Property

12

$7,640,316.00

8.391734059

Doctor

101 - Appraisal / Property

12

$7,640,316.00

3.560062175

Doctor

101 - Appraisal / Property

12

$7,640,316.00

10.54172367

Doctor

101 - Appraisal / Property

12

$7,640,316.00

9.829436374

Doctor

101 - Appraisal / Property

12

$7,640,316.00

8.48852325

Doctor

101 - Appraisal / Property

12

$7,640,316.00

2.486808137

Doctor

102 - Assets

65

$42,424,386.70

2.003564615

Doctor

102 - Assets

65

$42,424,386.70

2.467920179

Doctor

102 - Assets

65

$42,424,386.70

1.004643869

Doctor

102 - Assets

65

$42,424,386.70

1.912433068

Doctor

102 - Assets

65

$42,424,386.70

0.398209646

Doctor

102 - Assets

65

$42,424,386.70

0.777383071

Doctor

102 - Assets

65

$42,424,386.70

2.060135851

Doctor

102 - Assets

65

$42,424,386.70

1.293831314

Respected Advisor
Posts: 4,919

Re: Aggregate totals Duplicates in proc sql

You cannot avoid "duplicates" as long as you calculate expression

 

GROSS_LN_AMT/sum(GROSS_LN_AMT)*100

 

In fact, if you look at the results, the values of amt_pct are NOT duplicated.

 

It all depends on what you really need from this query.

PG
Ask a Question
Discussion stats
  • 1 reply
  • 189 views
  • 0 likes
  • 2 in conversation