topic Aggregate totals Duplicates in proc sql in SAS Programming

topic Aggregate totals Duplicates in proc sql in SAS Programming https://communities.sas.com/t5/SAS-Programming/Aggregate-totals-Duplicates-in-proc-sql/m-p/307453#M65846 <P><STRONG>proc</STRONG> <STRONG>sql</STRONG>;</P> <P>create table exc_cnt as</P> <P>select prog_ctgy,CODEDESC,count(ln_cnt) as cnt_cd,</P> <P>           sum(GROSS_LN_AMT) as gr_amt format=dollar12.2 ,</P> <P>           GROSS_LN_AMT/sum(GROSS_LN_AMT)*<STRONG>100</STRONG> as amt_pct</P> <P>           from loan7</P> <P>where CODEDESC ne ''</P> <P>group by prog_ctgy ,CODEDESC having cnt_cd</P> <P>;<STRONG>quit</STRONG>;</P> <P>Output issue with aggregates  how would i structure the code to avoid the duplicates</P> <TABLE width="493"> <TBODY> <TR> <TD width="75"> <P>prog_ctgy</P> </TD> <TD width="191"> <P>CODEDESC</P> </TD> <TD width="47"> <P>cnt_cd</P> </TD> <TD width="97"> <P>gr_amt</P> </TD> <TD width="84"> <P>amt_pct</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>7.866166792</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>11.79925019</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>11.77830865</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>12.89724142</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>6.904164697</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>5.456580592</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>8.391734059</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>3.560062175</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>10.54172367</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>9.829436374</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>8.48852325</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>2.486808137</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>2.003564615</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>2.467920179</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>1.004643869</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>1.912433068</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>0.398209646</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>0.777383071</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>2.060135851</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>1.293831314</P> </TD> </TR> </TBODY> </TABLE> Wed, 26 Oct 2016 16:54:56 GMT Q1983 2016-10-26T16:54:56Z Aggregate totals Duplicates in proc sql https://communities.sas.com/t5/SAS-Programming/Aggregate-totals-Duplicates-in-proc-sql/m-p/307453#M65846 <P><STRONG>proc</STRONG> <STRONG>sql</STRONG>;</P> <P>create table exc_cnt as</P> <P>select prog_ctgy,CODEDESC,count(ln_cnt) as cnt_cd,</P> <P>           sum(GROSS_LN_AMT) as gr_amt format=dollar12.2 ,</P> <P>           GROSS_LN_AMT/sum(GROSS_LN_AMT)*<STRONG>100</STRONG> as amt_pct</P> <P>           from loan7</P> <P>where CODEDESC ne ''</P> <P>group by prog_ctgy ,CODEDESC having cnt_cd</P> <P>;<STRONG>quit</STRONG>;</P> <P>Output issue with aggregates  how would i structure the code to avoid the duplicates</P> <TABLE width="493"> <TBODY> <TR> <TD width="75"> <P>prog_ctgy</P> </TD> <TD width="191"> <P>CODEDESC</P> </TD> <TD width="47"> <P>cnt_cd</P> </TD> <TD width="97"> <P>gr_amt</P> </TD> <TD width="84"> <P>amt_pct</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>7.866166792</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>11.79925019</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>11.77830865</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>12.89724142</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>6.904164697</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>5.456580592</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>8.391734059</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>3.560062175</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>10.54172367</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>9.829436374</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>8.48852325</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>101 - Appraisal / Property</P> </TD> <TD width="47"> <P>12</P> </TD> <TD width="97"> <P>$7,640,316.00</P> </TD> <TD width="84"> <P>2.486808137</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>2.003564615</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>2.467920179</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>1.004643869</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>1.912433068</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>0.398209646</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>0.777383071</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>2.060135851</P> </TD> </TR> <TR> <TD width="75"> <P>Doctor</P> </TD> <TD width="191"> <P>102 - Assets</P> </TD> <TD width="47"> <P>65</P> </TD> <TD width="97"> <P>$42,424,386.70</P> </TD> <TD width="84"> <P>1.293831314</P> </TD> </TR> </TBODY> </TABLE> Wed, 26 Oct 2016 16:54:56 GMT https://communities.sas.com/t5/SAS-Programming/Aggregate-totals-Duplicates-in-proc-sql/m-p/307453#M65846 Q1983 2016-10-26T16:54:56Z Re: Aggregate totals Duplicates in proc sql https://communities.sas.com/t5/SAS-Programming/Aggregate-totals-Duplicates-in-proc-sql/m-p/307454#M65847 <P>You cannot avoid "duplicates" as long as you calculate expression</P> <P> </P> <P>GROSS_LN_AMT/sum(GROSS_LN_AMT)*100</P> <P> </P> <P>In fact, if you look at the results, the values of amt_pct are NOT duplicated.</P> <P> </P> <P>It all depends on what you really need from this query.</P> Wed, 26 Oct 2016 17:06:48 GMT https://communities.sas.com/t5/SAS-Programming/Aggregate-totals-Duplicates-in-proc-sql/m-p/307454#M65847 PGStats 2016-10-26T17:06:48Z