topic Re: outliers in SAS Programming https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303750#M312437 <P>I do not know whether there is problem on my system or have you pasted everything inappropriately.</P><P>Data is flowing outside the intended place holder.</P> Tue, 11 Oct 2016 12:05:58 GMT RahulG 2016-10-11T12:05:58Z outliers https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303739#M312436 <P>hi ,</P><P>how to do outliers capping at 95th percentile in this case?</P><TABLE><TBODY><TR><TD>Variable</TD><TD>N</TD><TD>N Miss</TD><TD>Mean</TD><TD>Std Dev</TD><TD>Minimum</TD><TD>1st Pctl</TD><TD>5th Ptcl</TD><TD>10th Pctl</TD><TD>25th Pctl</TD><TD>50th Pctl</TD><TD>75th Pctl</TD><TD>90th Pctl</TD><TD>95th Pctl</TD><TD>99th Pctl</TD><TD>Maximum</TD></TR><TR><TD>BALANCE</TD><TD>8950</TD><TD>0</TD><TD>1564.47</TD><TD>2081.53</TD><TD>0</TD><TD>0.064811</TD><TD>8.80954</TD><TD>23.544808</TD><TD>128.25396</TD><TD>873.385231</TD><TD>2054.37</TD><TD>4338.58</TD><TD>5911.51</TD><TD>9342.16</TD><TD>19043.14</TD></TR><TR><TD>BALANCE_FREQUENCY</TD><TD>8950</TD><TD>0</TD><TD>0.8772707</TD><TD>0.236904</TD><TD>0</TD><TD>0.090909</TD><TD>0.272727</TD><TD>0.454545</TD><TD>0.888889</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>PURCHASES</TD><TD>8950</TD><TD>0</TD><TD>1003.2</TD><TD>2136.63</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>39.58</TD><TD>361.28</TD><TD>1110.17</TD><TD>2542.68</TD><TD>3999.92</TD><TD>9007.67</TD><TD>49039.57</TD></TR><TR><TD>ONEOFF_PURCHASES</TD><TD>8950</TD><TD>0</TD><TD>592.4373709</TD><TD>1659.89</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>38</TD><TD>577.83</TD><TD>1600.5</TD><TD>2675</TD><TD>6701.08</TD><TD>40761.25</TD></TR><TR><TD>INSTALLMENTS_PURCHASES</TD><TD>8950</TD><TD>0</TD><TD>411.0676447</TD><TD>904.3381152</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>89</TD><TD>468.65</TD><TD>1140.35</TD><TD>1753.08</TD><TD>3887</TD><TD>22500</TD></TR><TR><TD>CASH_ADVANCE</TD><TD>8950</TD><TD>0</TD><TD>978.8711125</TD><TD>2097.16</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1113.87</TD><TD>3069.79</TD><TD>4653.69</TD><TD>9596.32</TD><TD>47137.21</TD></TR><TR><TD>PURCHASES_FREQUENCY</TD><TD>8950</TD><TD>0</TD><TD>0.4903505</TD><TD>0.4013707</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.083333</TD><TD>0.5</TD><TD>0.916667</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>ONEOFF_PURCHASES_FREQUENCY</TD><TD>8950</TD><TD>0</TD><TD>0.2024577</TD><TD>0.2983361</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.083333</TD><TD>0.3</TD><TD>0.75</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>PURCHASES_INSTALLMENTS_FREQUENCY</TD><TD>8950</TD><TD>0</TD><TD>0.3644373</TD><TD>0.3974478</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.166667</TD><TD>0.75</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>CASH_ADVANCE_FREQUENCY</TD><TD>8950</TD><TD>0</TD><TD>0.1351442</TD><TD>0.2001214</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.222222</TD><TD>0.416667</TD><TD>0.583333</TD><TD>0.833333</TD><TD>1.5</TD></TR><TR><TD>CASH_ADVANCE_TRX</TD><TD>8950</TD><TD>0</TD><TD>3.2488268</TD><TD>6.8246467</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>4</TD><TD>10</TD><TD>15</TD><TD>29</TD><TD>123</TD></TR><TR><TD>PURCHASES_TRX</TD><TD>8950</TD><TD>0</TD><TD>14.7098324</TD><TD>24.8576491</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1</TD><TD>7</TD><TD>17</TD><TD>37</TD><TD>57</TD><TD>117</TD><TD>358</TD></TR><TR><TD>CREDIT_LIMIT</TD><TD>8949</TD><TD>1</TD><TD>4494.45</TD><TD>3638.82</TD><TD>50</TD><TD>500</TD><TD>1000</TD><TD>1200</TD><TD>1600</TD><TD>3000</TD><TD>6500</TD><TD>9500</TD><TD>12000</TD><TD>17000</TD><TD>30000</TD></TR><TR><TD>PAYMENTS</TD><TD>8950</TD><TD>0</TD><TD>1733.14</TD><TD>2895.06</TD><TD>0</TD><TD>0</TD><TD>89.921689</TD><TD>179.508581</TD><TD>383.273938</TD><TD>856.901546</TD><TD>1901.28</TD><TD>3925.29</TD><TD>6083.43</TD><TD>13652.75</TD><TD>50721.48</TD></TR><TR><TD>MINIMUM_PAYMENTS</TD><TD>8637</TD><TD>313</TD><TD>864.2065423</TD><TD>2372.45</TD><TD>0.019163</TD><TD>19.492021</TD><TD>73.203221</TD><TD>106.028116</TD><TD>169.123707</TD><TD>312.343947</TD><TD>825.485459</TD><TD>1781.1</TD><TD>2767.05</TD><TD>9170.43</TD><TD>76406.21</TD></TR><TR><TD>PRC_FULL_PAYMENT</TD><TD>8950</TD><TD>0</TD><TD>0.1537146</TD><TD>0.2924992</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.142857</TD><TD>0.6833335</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>TENURE</TD><TD>8950</TD><TD>0</TD><TD>11.5173184</TD><TD>1.3383308</TD><TD>6</TD><TD>6</TD><TD>8</TD><TD>10</TD><TD>12</TD><TD>12</TD><TD>12</TD><TD>12</TD><TD>12</TD><TD>12</TD><TD>12</TD></TR><TR><TD>Ratio_Balance_Limit</TD><TD>8949</TD><TD>1</TD><TD>0.3889264</TD><TD>0.3897223</TD><TD>0</TD><TD>0.000020637</TD><TD>0.0029404</TD><TD>0.0077911</TD><TD>0.0415267</TD><TD>0.3028696</TD><TD>0.7175819</TD><TD>0.9199151</TD><TD>0.9667606</TD><TD>1.0571536</TD><TD>15.9099511</TD></TR><TR><TD>payment_by_min</TD><TD>8637</TD><TD>313</TD><TD>9.3500701</TD><TD>120.2869146</TD><TD>0.000730423</TD><TD>0.0553129</TD><TD>0.454491</TD><TD>0.609355</TD><TD>0.9575493</TD><TD>2.1704953</TD><TD>6.2604302</TD><TD>13.3353681</TD><TD>21.4431139</TD><TD>50.6009237</TD><TD>6840.53</TD></TR><TR><TD>Average_Purchase</TD><TD>8950</TD><TD>0</TD><TD>73.8706831</TD><TD>160.5249391</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>11.98125</TD><TD>41.3939286</TD><TD>78.6744828</TD><TD>147.639</TD><TD>228.5714286</TD><TD>763.71</TD><TD>5981.67</TD></TR><TR><TD>Average_Cash_advance</TD><TD>8950</TD><TD>0</TD><TD>208.9051168</TD><TD>535.6555169</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>247.04197</TD><TD>561.180268</TD><TD>926.7585355</TD><TD>1909.68</TD><TD>14836.45</TD></TR></TBODY></TABLE><P>&nbsp;</P> Tue, 11 Oct 2016 10:17:55 GMT https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303739#M312436 Prateek1 2016-10-11T10:17:55Z Re: outliers https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303750#M312437 <P>I do not know whether there is problem on my system or have you pasted everything inappropriately.</P><P>Data is flowing outside the intended place holder.</P> Tue, 11 Oct 2016 12:05:58 GMT https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303750#M312437 RahulG 2016-10-11T12:05:58Z outlier treatment https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303751#M312439 <P>hi,</P><P>how to do outlier capping &nbsp;at 95th percentile?</P><P>this is a code&nbsp;</P><P>data cluster;<BR />set Y.Cd;<BR /><BR />if BALANCE &gt; 5911.51 then BALANCE = 5911.51 ;<BR />if PURCHASES &gt; 3999.92 then PURCHASES = 3999.92 ;<BR />if ONEOFF_PURCHASES &gt; 2675 then ONEOFF_PURCHASES = 2675 ;<BR />if INSTALLMENTS_PURCHASES &gt; 1753.08 then INSTALLMENTS_PURCHASES = 1753.08 ;<BR />if CASH_ADVANCE &gt; 4653.69 then CASH_ADVANCE = 4653.69 ;<BR />if CASH_ADVANCE_TRX &gt; 15 then CASH_ADVANCE_TRX = 15 ;</P><P>if PURCHASES_TRX &gt; 57 then PURCHASES_TRX = 57 ;</P><P>if CREDIT_LIMIT &gt; 12000 then CREDIT_LIMIT = 12000 ;</P><P>if PAYMENTS &gt; 6083.43 then PAYMENTS = 6083.43 ;<BR />if CASH_ADVANCE_FREQUENCY &gt; 0.58333 then CASH_ADVANCE_FREQUENCY = 0.58333 ;<BR />if MINIMUM_PAYMENTS &gt; 2767.05 then MINIMUM_PAYMENTS =2767.05;<BR />if Payment_by_min &gt; 21.44311 then Payment_by_min =21.44311;<BR />if Average_Purchase &gt; 228.571 then Average_Purchase = 228.571;<BR />if Average_Cash_Advance &gt; 1365.19 then Average_Cash_Advance= 926.7585;<BR />run;</P><TABLE><TBODY><TR><TD>Variable</TD><TD>N</TD><TD>N Miss</TD><TD>Mean</TD><TD>Std Dev</TD><TD>Minimum</TD><TD>1st Pctl</TD><TD>5th Ptcl</TD><TD>10th Pctl</TD><TD>25th Pctl</TD><TD>50th Pctl</TD><TD>75th Pctl</TD><TD>90th Pctl</TD><TD>95th Pctl</TD><TD>99th Pctl</TD><TD>Maximum</TD></TR><TR><TD>BALANCE</TD><TD>8950</TD><TD>0</TD><TD>1564.47</TD><TD>2081.53</TD><TD>0</TD><TD>0.06481</TD><TD>8.80954</TD><TD>23.544808</TD><TD>128.25396</TD><TD>873.385231</TD><TD>2054.37</TD><TD>4338.58</TD><TD>5911.51</TD><TD>9342.16</TD><TD>19043.14</TD></TR><TR><TD>BALANCE_FREQUENCY</TD><TD>8950</TD><TD>0</TD><TD>0.8772707</TD><TD>0.236904</TD><TD>0</TD><TD>0.09091</TD><TD>0.272727</TD><TD>0.454545</TD><TD>0.888889</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>PURCHASES</TD><TD>8950</TD><TD>0</TD><TD>1003.2</TD><TD>2136.63</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>39.58</TD><TD>361.28</TD><TD>1110.17</TD><TD>2542.68</TD><TD>3999.92</TD><TD>9007.67</TD><TD>49039.57</TD></TR><TR><TD>ONEOFF_PURCHASES</TD><TD>8950</TD><TD>0</TD><TD>592.4373709</TD><TD>1659.89</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>38</TD><TD>577.83</TD><TD>1600.5</TD><TD>2675</TD><TD>6701.08</TD><TD>40761.25</TD></TR><TR><TD>INSTALLMENTS_PURCHASES</TD><TD>8950</TD><TD>0</TD><TD>411.0676447</TD><TD>904.3381</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>89</TD><TD>468.65</TD><TD>1140.35</TD><TD>1753.08</TD><TD>3887</TD><TD>22500</TD></TR><TR><TD>CASH_ADVANCE</TD><TD>8950</TD><TD>0</TD><TD>978.8711125</TD><TD>2097.16</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1113.87</TD><TD>3069.79</TD><TD>4653.69</TD><TD>9596.32</TD><TD>47137.21</TD></TR><TR><TD>PURCHASES_FREQUENCY</TD><TD>8950</TD><TD>0</TD><TD>0.4903505</TD><TD>0.401371</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.083333</TD><TD>0.5</TD><TD>0.916667</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>ONEOFF_PURCHASES_FREQUENCY</TD><TD>8950</TD><TD>0</TD><TD>0.2024577</TD><TD>0.298336</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.083333</TD><TD>0.3</TD><TD>0.75</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>PURCHASES_INSTALLMENTS_FREQUENCY</TD><TD>8950</TD><TD>0</TD><TD>0.3644373</TD><TD>0.397448</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.166667</TD><TD>0.75</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>CASH_ADVANCE_FREQUENCY</TD><TD>8950</TD><TD>0</TD><TD>0.1351442</TD><TD>0.200121</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.222222</TD><TD>0.416667</TD><TD>0.583333</TD><TD>0.833333</TD><TD>1.5</TD></TR><TR><TD>CASH_ADVANCE_TRX</TD><TD>8950</TD><TD>0</TD><TD>3.2488268</TD><TD>6.824647</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>4</TD><TD>10</TD><TD>15</TD><TD>29</TD><TD>123</TD></TR><TR><TD>PURCHASES_TRX</TD><TD>8950</TD><TD>0</TD><TD>14.7098324</TD><TD>24.85765</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1</TD><TD>7</TD><TD>17</TD><TD>37</TD><TD>57</TD><TD>117</TD><TD>358</TD></TR><TR><TD>CREDIT_LIMIT</TD><TD>8949</TD><TD>1</TD><TD>4494.45</TD><TD>3638.82</TD><TD>50</TD><TD>500</TD><TD>1000</TD><TD>1200</TD><TD>1600</TD><TD>3000</TD><TD>6500</TD><TD>9500</TD><TD>12000</TD><TD>17000</TD><TD>30000</TD></TR><TR><TD>PAYMENTS</TD><TD>8950</TD><TD>0</TD><TD>1733.14</TD><TD>2895.06</TD><TD>0</TD><TD>0</TD><TD>89.921689</TD><TD>179.508581</TD><TD>383.273938</TD><TD>856.901546</TD><TD>1901.28</TD><TD>3925.29</TD><TD>6083.43</TD><TD>13652.75</TD><TD>50721.48</TD></TR><TR><TD>MINIMUM_PAYMENTS</TD><TD>8637</TD><TD>313</TD><TD>864.2065423</TD><TD>2372.45</TD><TD>0.02</TD><TD>19.492</TD><TD>73.203221</TD><TD>106.028116</TD><TD>169.123707</TD><TD>312.343947</TD><TD>825.485459</TD><TD>1781.1</TD><TD>2767.05</TD><TD>9170.43</TD><TD>76406.21</TD></TR><TR><TD>PRC_FULL_PAYMENT</TD><TD>8950</TD><TD>0</TD><TD>0.1537146</TD><TD>0.292499</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.142857</TD><TD>0.6833335</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>TENURE</TD><TD>8950</TD><TD>0</TD><TD>11.5173184</TD><TD>1.338331</TD><TD>6</TD><TD>6</TD><TD>8</TD><TD>10</TD><TD>12</TD><TD>12</TD><TD>12</TD><TD>12</TD><TD>12</TD><TD>12</TD><TD>12</TD></TR><TR><TD>Ratio_Balance_Limit</TD><TD>8949</TD><TD>1</TD><TD>0.3889264</TD><TD>0.389722</TD><TD>0</TD><TD>2.1E-05</TD><TD>0.0029404</TD><TD>0.0077911</TD><TD>0.0415267</TD><TD>0.3028696</TD><TD>0.7175819</TD><TD>0.9199151</TD><TD>0.9667606</TD><TD>1.0571536</TD><TD>15.9099511</TD></TR><TR><TD>payment_by_min</TD><TD>8637</TD><TD>313</TD><TD>9.3500701</TD><TD>120.2869</TD><TD>0</TD><TD>0.05531</TD><TD>0.454491</TD><TD>0.609355</TD><TD>0.9575493</TD><TD>2.1704953</TD><TD>6.2604302</TD><TD>13.3353681</TD><TD>21.4431139</TD><TD>50.6009237</TD><TD>6840.53</TD></TR><TR><TD>Average_Purchase</TD><TD>8950</TD><TD>0</TD><TD>73.8706831</TD><TD>160.5249</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>11.98125</TD><TD>41.3939286</TD><TD>78.6744828</TD><TD>147.639</TD><TD>228.5714286</TD><TD>763.71</TD><TD>5981.67</TD></TR><TR><TD>Average_Cash_advance</TD><TD>8950</TD><TD>0</TD><TD>208.9051168</TD><TD>535.6555</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>247.04197</TD><TD>561.180268</TD><TD>926.7585355</TD><TD>1909.68</TD><TD>14836.45</TD></TR></TBODY></TABLE><P>&nbsp;</P> Tue, 11 Oct 2016 12:06:03 GMT https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303751#M312439 Prateek1 2016-10-11T12:06:03Z Re: outliers https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303753#M312438 <P>What kind of outpu do you want ? you want use 95th percentile to replace any value greater than 95th percentile?</P> <P>Or you want winsorize data ? Here is IML code:</P> <P>&nbsp;</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">data have; do i=1 to 100; a=ceil(ranuni(1)*100); b=ceil(ranuni(2)*100); output; end; drop i; run; %let low=0.05 ; %let high=0.95 ; proc iml; use have; read all var _num_ into x[c=vname]; close have; call qntl(q,x,{&amp;low ,&amp;high}); do i=1 to ncol(x); x[loc(x[,i]&lt;q[1,i]),i]=q[1,i]; x[loc(x[,i]&gt;q[2,i]),i]=q[2,i]; end; create want from x[c=vname]; append from x; close want; quit;</CODE></PRE> <P>&nbsp;</P> Tue, 11 Oct 2016 12:08:40 GMT https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303753#M312438 Ksharp 2016-10-11T12:08:40Z Re: outlier treatment https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303761#M312440 <P>So you only want replace any value above 95th percentile &nbsp;with 95th percentile.</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas"> %let low=0.05 ; %let high=0.95 ; proc iml; use have; read all var _num_ into x[c=vname]; close have; call qntl(q,x,{&amp;low ,&amp;high}); do i=1 to ncol(x); *x[loc(x[,i]&lt;q[1,i]),i]=q[1,i]; x[loc(x[,i]&gt;q[2,i]),i]=q[2,i]; end; create want from x[c=vname]; append from x; close want; quit;</CODE></PRE> Tue, 11 Oct 2016 12:31:22 GMT https://communities.sas.com/t5/SAS-Programming/outliers/m-p/303761#M312440 Ksharp 2016-10-11T12:31:22Z