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.
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.
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.