BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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

1 REPLY 1
PGStats
Opal | Level 21

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

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
  • 1 reply
  • 952 views
  • 0 likes
  • 2 in conversation