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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.