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 |
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.