topic Re: Remove matching credit and debit entries in a dataset in New SAS User https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535191#M6424 <P>My understanding is you are basically discarding those transactions that are reversed. Is that correct?</P> <P>&nbsp;</P> <P>if yes, then I am wondering why this one is being discarded in your output</P> <P>&nbsp;</P> <TABLE> <TBODY> <TR> <TD><SPAN>60016848</SPAN></TD> <TD><SPAN>18.04</SPAN></TD> <TD><SPAN>-87.57</SPAN></TD> <TD><SPAN>18Jan2019</SPAN></TD> <TD><SPAN>600</SPAN></TD> </TR> </TBODY> </TABLE> Wed, 13 Feb 2019 14:21:50 GMT novinosrin 2019-02-13T14:21:50Z Remove matching credit and debit entries in a dataset https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/534961#M6392 <P>I need to remove matching credit and debit entries from a dataset what is the easiest way to do this?</P><P><U><STRONG>HAVE:</STRONG></U></P><TABLE><TBODY><TR><TD>LOAN_NBR</TD><TD>INT_AMT_PAID</TD><TD>PRIN_AMT_PAID</TD><TD>EFF_FROM_DT</TD></TR><TR><TD>60016100</TD><TD>20.58</TD><TD>-98.50</TD><TD>07Jan2019</TD></TR><TR><TD>60016101</TD><TD>29.72</TD><TD>-80.28</TD><TD>07Jan2019</TD></TR><TR><TD>60016101</TD><TD>15.19</TD><TD>-94.81</TD><TD>28Jan2019</TD></TR><TR><TD>60016102</TD><TD>52.70</TD><TD>-57.35</TD><TD>16Jan2019</TD></TR><TR><TD>60016102</TD><TD>0.00</TD><TD>-110.05</TD><TD>16Jan2019</TD></TR><TR><TD>60016103</TD><TD>121.08</TD><TD>-235.92</TD><TD>04Jan2019</TD></TR><TR><TD>60016122</TD><TD>41.29</TD><TD>-89.06</TD><TD>17Jan2019</TD></TR><TR><TD>60016122</TD><TD>-41.29</TD><TD>89.06</TD><TD>24Jan2019</TD></TR><TR><TD>60016122</TD><TD>57.27</TD><TD>-73.08</TD><TD>29Jan2019</TD></TR><TR><TD>60016212</TD><TD>29.24</TD><TD>-73.85</TD><TD>09Jan2019</TD></TR><TR><TD>60016212</TD><TD>-29.24</TD><TD>73.85</TD><TD>17Jan2019</TD></TR><TR><TD>60016274</TD><TD>142.96</TD><TD>-186.55</TD><TD>11Jan2019</TD></TR><TR><TD>60016274</TD><TD>-142.96</TD><TD>186.55</TD><TD>21Jan2019</TD></TR><TR><TD>60016326</TD><TD>45.99</TD><TD>-96.08</TD><TD>03Jan2019</TD></TR><TR><TD>60016326</TD><TD>-45.99</TD><TD>96.08</TD><TD>08Jan2019</TD></TR><TR><TD>60016326</TD><TD>57.48</TD><TD>-84.59</TD><TD>10Jan2019</TD></TR><TR><TD>60016468</TD><TD>61.45</TD><TD>-113.64</TD><TD>31Dec2018</TD></TR><TR><TD>60016468</TD><TD>-61.45</TD><TD>113.64</TD><TD>31Dec2018</TD></TR><TR><TD>60016501</TD><TD>17.69</TD><TD>-80.70</TD><TD>15Jan2019</TD></TR><TR><TD>60016501</TD><TD>18.76</TD><TD>-79.63</TD><TD>17Jan2019</TD></TR><TR><TD>60016501</TD><TD>-17.69</TD><TD>80.70</TD><TD>17Jan2019</TD></TR><TR><TD>60016701</TD><TD>18.76</TD><TD>-81.24</TD><TD>08Jan2019</TD></TR><TR><TD>60016701</TD><TD>-18.76</TD><TD>81.24</TD><TD>14Jan2019</TD></TR><TR><TD>60016701</TD><TD>26.48</TD><TD>-73.52</TD><TD>22Jan2019</TD></TR><TR><TD>60016701</TD><TD>3.75</TD><TD>-96.25</TD><TD>29Jan2019</TD></TR><TR><TD>60016760</TD><TD>24.25</TD><TD>-102.86</TD><TD>31Dec2018</TD></TR><TR><TD>60016760</TD><TD>5.15</TD><TD>-121.96</TD><TD>07Jan2019</TD></TR><TR><TD>60016760</TD><TD>-5.15</TD><TD>121.96</TD><TD>14Jan2019</TD></TR><TR><TD>60016760</TD><TD>13.24</TD><TD>-113.87</TD><TD>18Jan2019</TD></TR><TR><TD>60016848</TD><TD>32.63</TD><TD>-77.37</TD><TD>21Dec2018</TD></TR><TR><TD>60016848</TD><TD>50.90</TD><TD>-55.10</TD><TD>04Jan2019</TD></TR><TR><TD>60016848</TD><TD>-32.63</TD><TD>77.37</TD><TD>04Jan2019</TD></TR><TR><TD>60016848</TD><TD>18.04</TD><TD>-87.57</TD><TD>18Jan2019</TD></TR></TBODY></TABLE><P><U><STRONG>WANT:</STRONG></U></P><TABLE><TBODY><TR><TD>LOAN_NBR</TD><TD>INT_AMT_PAID</TD><TD>PRIN_AMT_PAID</TD><TD>EFF_FROM_DT</TD></TR><TR><TD><STRONG>60016100</STRONG></TD><TD><STRONG>20.58</STRONG></TD><TD><STRONG>-98.50</STRONG></TD><TD><STRONG>07Jan2019</STRONG></TD></TR><TR><TD><STRONG>60016101</STRONG></TD><TD><STRONG>29.72</STRONG></TD><TD><STRONG>-80.28</STRONG></TD><TD><STRONG>07Jan2019</STRONG></TD></TR><TR><TD><STRONG>60016101</STRONG></TD><TD><STRONG>15.19</STRONG></TD><TD><STRONG>-94.81</STRONG></TD><TD><STRONG>28Jan2019</STRONG></TD></TR><TR><TD><STRONG>60016102</STRONG></TD><TD><STRONG>52.70</STRONG></TD><TD><STRONG>-57.35</STRONG></TD><TD><STRONG>16Jan2019</STRONG></TD></TR><TR><TD><STRONG>60016102</STRONG></TD><TD><STRONG>0.00</STRONG></TD><TD><STRONG>-110.05</STRONG></TD><TD><STRONG>16Jan2019</STRONG></TD></TR><TR><TD><STRONG>60016103</STRONG></TD><TD><STRONG>121.08</STRONG></TD><TD><STRONG>-235.92</STRONG></TD><TD><STRONG>04Jan2019</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016122</SPAN></TD><TD><SPAN style="text-decoration: line-through;">41.29</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-89.06</SPAN></TD><TD><SPAN style="text-decoration: line-through;">17Jan2019</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016122</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-41.29</SPAN></TD><TD><SPAN style="text-decoration: line-through;">89.06</SPAN></TD><TD><SPAN style="text-decoration: line-through;">24Jan2019</SPAN></TD></TR><TR><TD><STRONG>60016122</STRONG></TD><TD><STRONG>57.27</STRONG></TD><TD><STRONG>-73.08</STRONG></TD><TD><STRONG>29Jan2019</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016212</SPAN></TD><TD><SPAN style="text-decoration: line-through;">29.24</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-73.85</SPAN></TD><TD><SPAN style="text-decoration: line-through;">09Jan2019</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016212</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-29.24</SPAN></TD><TD><SPAN style="text-decoration: line-through;">73.85</SPAN></TD><TD><SPAN style="text-decoration: line-through;">17Jan2019</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016274</SPAN></TD><TD><SPAN style="text-decoration: line-through;">142.96</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-186.55</SPAN></TD><TD><SPAN style="text-decoration: line-through;">11Jan2019</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016274</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-142.96</SPAN></TD><TD><SPAN style="text-decoration: line-through;">186.55</SPAN></TD><TD><SPAN style="text-decoration: line-through;">21Jan2019</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016326</SPAN></TD><TD><SPAN style="text-decoration: line-through;">45.99</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-96.08</SPAN></TD><TD><SPAN style="text-decoration: line-through;">03Jan2019</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016326</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-45.99</SPAN></TD><TD><SPAN style="text-decoration: line-through;">96.08</SPAN></TD><TD><SPAN style="text-decoration: line-through;">08Jan2019</SPAN></TD></TR><TR><TD><STRONG>60016326</STRONG></TD><TD><STRONG>57.48</STRONG></TD><TD><STRONG>-84.59</STRONG></TD><TD><STRONG>10Jan2019</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016468</SPAN></TD><TD><SPAN style="text-decoration: line-through;">61.45</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-113.64</SPAN></TD><TD><SPAN style="text-decoration: line-through;">31Dec2018</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016468</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-61.45</SPAN></TD><TD><SPAN style="text-decoration: line-through;">113.64</SPAN></TD><TD><SPAN style="text-decoration: line-through;">31Dec2018</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016501</SPAN></TD><TD><SPAN style="text-decoration: line-through;">17.69</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-80.70</SPAN></TD><TD><SPAN style="text-decoration: line-through;">15Jan2019</SPAN></TD></TR><TR><TD><STRONG>60016501</STRONG></TD><TD><STRONG>18.76</STRONG></TD><TD><STRONG>-79.63</STRONG></TD><TD><STRONG>17Jan2019</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016501</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-17.69</SPAN></TD><TD><SPAN style="text-decoration: line-through;">80.70</SPAN></TD><TD><SPAN style="text-decoration: line-through;">17Jan2019</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016701</SPAN></TD><TD><SPAN style="text-decoration: line-through;">18.76</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-81.24</SPAN></TD><TD><SPAN style="text-decoration: line-through;">08Jan2019</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016701</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-18.76</SPAN></TD><TD><SPAN style="text-decoration: line-through;">81.24</SPAN></TD><TD><SPAN style="text-decoration: line-through;">14Jan2019</SPAN></TD></TR><TR><TD><STRONG>60016701</STRONG></TD><TD><STRONG>26</STRONG></TD><TD><STRONG>-73.52</STRONG></TD><TD><STRONG>43487.00</STRONG></TD></TR><TR><TD><STRONG>60016701</STRONG></TD><TD><STRONG>4</STRONG></TD><TD><STRONG>-96.25</STRONG></TD><TD><STRONG>43494.00</STRONG></TD></TR><TR><TD><STRONG>60016760</STRONG></TD><TD><STRONG>24</STRONG></TD><TD><STRONG>-102.86</STRONG></TD><TD><STRONG>43465.00</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016760</SPAN></TD><TD><SPAN style="text-decoration: line-through;">5.15</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-121.96</SPAN></TD><TD><SPAN style="text-decoration: line-through;">07Jan2019</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016760</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-5.15</SPAN></TD><TD><SPAN style="text-decoration: line-through;">121.96</SPAN></TD><TD><SPAN style="text-decoration: line-through;">14Jan2019</SPAN></TD></TR><TR><TD><STRONG>60016760</STRONG></TD><TD><STRONG>13.24</STRONG></TD><TD><STRONG>-113.87</STRONG></TD><TD><STRONG>18Jan2019</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016848</SPAN></TD><TD><SPAN style="text-decoration: line-through;">32.63</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-77.37</SPAN></TD><TD><SPAN style="text-decoration: line-through;">21Dec2018</SPAN></TD></TR><TR><TD><STRONG>60016848</STRONG></TD><TD><STRONG>50.90</STRONG></TD><TD><STRONG>-55.10</STRONG></TD><TD><STRONG>04Jan2019</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016848</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-32.63</SPAN></TD><TD><SPAN style="text-decoration: line-through;">77.37</SPAN></TD><TD><SPAN style="text-decoration: line-through;">04Jan2019</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016848</SPAN></TD><TD><SPAN style="text-decoration: line-through;">18.04</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-87.57</SPAN></TD><TD><SPAN style="text-decoration: line-through;">18Jan2019</SPAN></TD></TR></TBODY></TABLE> Tue, 12 Feb 2019 19:03:53 GMT https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/534961#M6392 sivakoya 2019-02-12T19:03:53Z Re: Remove matching credit and debit entries in a dataset https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/534970#M6394 <P>Hi&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/31142">@sivakoya</a>&nbsp; &nbsp;Where's the matching credit and debit entry for this?</P> <P>&nbsp;</P> <TABLE> <TBODY> <TR> <TD><SPAN>60016848</SPAN></TD> <TD><SPAN>18.04</SPAN></TD> <TD><SPAN>-87.57</SPAN></TD> <TD><SPAN>18Jan2019</SPAN></TD> </TR> </TBODY> </TABLE> Tue, 12 Feb 2019 19:31:52 GMT https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/534970#M6394 novinosrin 2019-02-12T19:31:52Z Re: Remove matching credit and debit entries in a dataset https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/534998#M6406 You need another identifier to do this properly, otherwise it's a best guess. You don't have any other variables indicating a transactions reversal? Tue, 12 Feb 2019 20:45:20 GMT https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/534998#M6406 Reeza 2019-02-12T20:45:20Z Re: Remove matching credit and debit entries in a dataset https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535174#M6419 <P><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/138205">@novinosrin</a>&nbsp;not all transactions will have reversals</P> Wed, 13 Feb 2019 13:04:34 GMT https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535174#M6419 sivakoya 2019-02-13T13:04:34Z Re: Remove matching credit and debit entries in a dataset https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535176#M6420 <P>There is transaction code. 600 is for normal transaction and 608 is for reversal. if that's what you meant by another identifier</P><P>&nbsp;</P><P>HAVE:</P><TABLE><TBODY><TR><TD>LOAN_NBR</TD><TD>INT_AMT_PAID</TD><TD>PRIN_AMT_PAID</TD><TD>EFF_FROM_DT</TD><TD>TRAN_CODE</TD></TR><TR><TD>60016100</TD><TD>20.58</TD><TD>-98.50</TD><TD>07Jan2019</TD><TD>600</TD></TR><TR><TD>60016101</TD><TD>29.72</TD><TD>-80.28</TD><TD>07Jan2019</TD><TD>600</TD></TR><TR><TD>60016101</TD><TD>15.19</TD><TD>-94.81</TD><TD>28Jan2019</TD><TD>600</TD></TR><TR><TD>60016102</TD><TD>52.70</TD><TD>-57.35</TD><TD>16Jan2019</TD><TD>600</TD></TR><TR><TD>60016102</TD><TD>0.00</TD><TD>-110.05</TD><TD>16Jan2019</TD><TD>600</TD></TR><TR><TD>60016103</TD><TD>121.08</TD><TD>-235.92</TD><TD>04Jan2019</TD><TD>600</TD></TR><TR><TD>60016122</TD><TD>41.29</TD><TD>-89.06</TD><TD>17Jan2019</TD><TD>600</TD></TR><TR><TD>60016122</TD><TD>-41.29</TD><TD>89.06</TD><TD>24Jan2019</TD><TD>608</TD></TR><TR><TD>60016122</TD><TD>57.27</TD><TD>-73.08</TD><TD>29Jan2019</TD><TD>600</TD></TR><TR><TD>60016212</TD><TD>29.24</TD><TD>-73.85</TD><TD>09Jan2019</TD><TD>600</TD></TR><TR><TD>60016212</TD><TD>-29.24</TD><TD>73.85</TD><TD>17Jan2019</TD><TD>608</TD></TR><TR><TD>60016274</TD><TD>142.96</TD><TD>-186.55</TD><TD>11Jan2019</TD><TD>600</TD></TR><TR><TD>60016274</TD><TD>-142.96</TD><TD>186.55</TD><TD>21Jan2019</TD><TD>608</TD></TR><TR><TD>60016326</TD><TD>45.99</TD><TD>-96.08</TD><TD>03Jan2019</TD><TD>600</TD></TR><TR><TD>60016326</TD><TD>-45.99</TD><TD>96.08</TD><TD>08Jan2019</TD><TD>608</TD></TR><TR><TD>60016326</TD><TD>57.48</TD><TD>-84.59</TD><TD>10Jan2019</TD><TD>600</TD></TR><TR><TD>60016468</TD><TD>61.45</TD><TD>-113.64</TD><TD>31Dec2018</TD><TD>600</TD></TR><TR><TD>60016468</TD><TD>-61.45</TD><TD>113.64</TD><TD>31Dec2018</TD><TD>608</TD></TR><TR><TD>60016501</TD><TD>17.69</TD><TD>-80.70</TD><TD>15Jan2019</TD><TD>600</TD></TR><TR><TD>60016501</TD><TD>18.76</TD><TD>-79.63</TD><TD>17Jan2019</TD><TD>600</TD></TR><TR><TD>60016501</TD><TD>-17.69</TD><TD>80.70</TD><TD>17Jan2019</TD><TD>608</TD></TR><TR><TD>60016701</TD><TD>18.76</TD><TD>-81.24</TD><TD>08Jan2019</TD><TD>600</TD></TR><TR><TD>60016701</TD><TD>-18.76</TD><TD>81.24</TD><TD>14Jan2019</TD><TD>608</TD></TR><TR><TD>60016701</TD><TD>26.48</TD><TD>-73.52</TD><TD>22Jan2019</TD><TD>600</TD></TR><TR><TD>60016701</TD><TD>3.75</TD><TD>-96.25</TD><TD>29Jan2019</TD><TD>600</TD></TR><TR><TD>60016760</TD><TD>24.25</TD><TD>-102.86</TD><TD>31Dec2018</TD><TD>600</TD></TR><TR><TD>60016760</TD><TD>5.15</TD><TD>-121.96</TD><TD>07Jan2019</TD><TD>600</TD></TR><TR><TD>60016760</TD><TD>-5.15</TD><TD>121.96</TD><TD>14Jan2019</TD><TD>608</TD></TR><TR><TD>60016760</TD><TD>13.24</TD><TD>-113.87</TD><TD>18Jan2019</TD><TD>600</TD></TR><TR><TD>60016848</TD><TD>32.63</TD><TD>-77.37</TD><TD>21Dec2018</TD><TD>600</TD></TR><TR><TD>60016848</TD><TD>50.90</TD><TD>-55.10</TD><TD>04Jan2019</TD><TD>600</TD></TR><TR><TD>60016848</TD><TD>-32.63</TD><TD>77.37</TD><TD>04Jan2019</TD><TD>608</TD></TR><TR><TD>60016848</TD><TD>18.04</TD><TD>-87.57</TD><TD>18Jan2019</TD><TD>600</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>WANT:</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>LOAN_NBR</TD><TD>INT_AMT_PAID</TD><TD>PRIN_AMT_PAID</TD><TD>EFF_FROM_DT</TD><TD>TRAN_CODE</TD></TR><TR><TD><STRONG>60016100</STRONG></TD><TD><STRONG>20.58</STRONG></TD><TD><STRONG>-98.50</STRONG></TD><TD><STRONG>07Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><STRONG>60016101</STRONG></TD><TD><STRONG>29.72</STRONG></TD><TD><STRONG>-80.28</STRONG></TD><TD><STRONG>07Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><STRONG>60016101</STRONG></TD><TD><STRONG>15.19</STRONG></TD><TD><STRONG>-94.81</STRONG></TD><TD><STRONG>28Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><STRONG>60016102</STRONG></TD><TD><STRONG>52.70</STRONG></TD><TD><STRONG>-57.35</STRONG></TD><TD><STRONG>16Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><STRONG>60016102</STRONG></TD><TD><STRONG>0.00</STRONG></TD><TD><STRONG>-110.05</STRONG></TD><TD><STRONG>16Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><STRONG>60016103</STRONG></TD><TD><STRONG>121.08</STRONG></TD><TD><STRONG>-235.92</STRONG></TD><TD><STRONG>04Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016122</SPAN></TD><TD><SPAN style="text-decoration: line-through;">41.29</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-89.06</SPAN></TD><TD><SPAN style="text-decoration: line-through;">17Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">600</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016122</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-41.29</SPAN></TD><TD><SPAN style="text-decoration: line-through;">89.06</SPAN></TD><TD><SPAN style="text-decoration: line-through;">24Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">608</SPAN></TD></TR><TR><TD><STRONG>60016122</STRONG></TD><TD><STRONG>57.27</STRONG></TD><TD><STRONG>-73.08</STRONG></TD><TD><STRONG>29Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016212</SPAN></TD><TD><SPAN style="text-decoration: line-through;">29.24</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-73.85</SPAN></TD><TD><SPAN style="text-decoration: line-through;">09Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">600</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016212</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-29.24</SPAN></TD><TD><SPAN style="text-decoration: line-through;">73.85</SPAN></TD><TD><SPAN style="text-decoration: line-through;">17Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">608</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016274</SPAN></TD><TD><SPAN style="text-decoration: line-through;">142.96</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-186.55</SPAN></TD><TD><SPAN style="text-decoration: line-through;">11Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">600</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016274</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-142.96</SPAN></TD><TD><SPAN style="text-decoration: line-through;">186.55</SPAN></TD><TD><SPAN style="text-decoration: line-through;">21Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">608</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016326</SPAN></TD><TD><SPAN style="text-decoration: line-through;">45.99</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-96.08</SPAN></TD><TD><SPAN style="text-decoration: line-through;">03Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">600</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016326</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-45.99</SPAN></TD><TD><SPAN style="text-decoration: line-through;">96.08</SPAN></TD><TD><SPAN style="text-decoration: line-through;">08Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">608</SPAN></TD></TR><TR><TD><STRONG>60016326</STRONG></TD><TD><STRONG>57.48</STRONG></TD><TD><STRONG>-84.59</STRONG></TD><TD><STRONG>10Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016468</SPAN></TD><TD><SPAN style="text-decoration: line-through;">61.45</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-113.64</SPAN></TD><TD><SPAN style="text-decoration: line-through;">31Dec2018</SPAN></TD><TD><SPAN style="text-decoration: line-through;">600</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016468</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-61.45</SPAN></TD><TD><SPAN style="text-decoration: line-through;">113.64</SPAN></TD><TD><SPAN style="text-decoration: line-through;">31Dec2018</SPAN></TD><TD><SPAN style="text-decoration: line-through;">608</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016501</SPAN></TD><TD><SPAN style="text-decoration: line-through;">17.69</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-80.70</SPAN></TD><TD><SPAN style="text-decoration: line-through;">15Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">600</SPAN></TD></TR><TR><TD><STRONG>60016501</STRONG></TD><TD><STRONG>18.76</STRONG></TD><TD><STRONG>-79.63</STRONG></TD><TD><STRONG>17Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016501</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-17.69</SPAN></TD><TD><SPAN style="text-decoration: line-through;">80.70</SPAN></TD><TD><SPAN style="text-decoration: line-through;">17Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">608</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016701</SPAN></TD><TD><SPAN style="text-decoration: line-through;">18.76</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-81.24</SPAN></TD><TD><SPAN style="text-decoration: line-through;">08Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">600</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016701</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-18.76</SPAN></TD><TD><SPAN style="text-decoration: line-through;">81.24</SPAN></TD><TD><SPAN style="text-decoration: line-through;">14Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">608</SPAN></TD></TR><TR><TD><STRONG>60016701</STRONG></TD><TD><STRONG>26</STRONG></TD><TD><STRONG>-73.52</STRONG></TD><TD><STRONG>43487.00</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><STRONG>60016701</STRONG></TD><TD><STRONG>4</STRONG></TD><TD><STRONG>-96.25</STRONG></TD><TD><STRONG>43494.00</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><STRONG>60016760</STRONG></TD><TD><STRONG>24</STRONG></TD><TD><STRONG>-102.86</STRONG></TD><TD><STRONG>43465.00</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016760</SPAN></TD><TD><SPAN style="text-decoration: line-through;">5.15</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-121.96</SPAN></TD><TD><SPAN style="text-decoration: line-through;">07Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">600</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016760</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-5.15</SPAN></TD><TD><SPAN style="text-decoration: line-through;">121.96</SPAN></TD><TD><SPAN style="text-decoration: line-through;">14Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">608</SPAN></TD></TR><TR><TD><STRONG>60016760</STRONG></TD><TD><STRONG>13.24</STRONG></TD><TD><STRONG>-113.87</STRONG></TD><TD><STRONG>18Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016848</SPAN></TD><TD><SPAN style="text-decoration: line-through;">32.63</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-77.37</SPAN></TD><TD><SPAN style="text-decoration: line-through;">21Dec2018</SPAN></TD><TD><SPAN style="text-decoration: line-through;">600</SPAN></TD></TR><TR><TD><STRONG>60016848</STRONG></TD><TD><STRONG>50.90</STRONG></TD><TD><STRONG>-55.10</STRONG></TD><TD><STRONG>04Jan2019</STRONG></TD><TD><STRONG>600</STRONG></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016848</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-32.63</SPAN></TD><TD><SPAN style="text-decoration: line-through;">77.37</SPAN></TD><TD><SPAN style="text-decoration: line-through;">04Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">608</SPAN></TD></TR><TR><TD><SPAN style="text-decoration: line-through;">60016848</SPAN></TD><TD><SPAN style="text-decoration: line-through;">18.04</SPAN></TD><TD><SPAN style="text-decoration: line-through;">-87.57</SPAN></TD><TD><SPAN style="text-decoration: line-through;">18Jan2019</SPAN></TD><TD><SPAN style="text-decoration: line-through;">600</SPAN></TD></TR></TBODY></TABLE><P>&nbsp;</P> Wed, 13 Feb 2019 13:07:57 GMT https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535176#M6420 sivakoya 2019-02-13T13:07:57Z Re: Remove matching credit and debit entries in a dataset https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535191#M6424 <P>My understanding is you are basically discarding those transactions that are reversed. Is that correct?</P> <P>&nbsp;</P> <P>if yes, then I am wondering why this one is being discarded in your output</P> <P>&nbsp;</P> <TABLE> <TBODY> <TR> <TD><SPAN>60016848</SPAN></TD> <TD><SPAN>18.04</SPAN></TD> <TD><SPAN>-87.57</SPAN></TD> <TD><SPAN>18Jan2019</SPAN></TD> <TD><SPAN>600</SPAN></TD> </TR> </TBODY> </TABLE> Wed, 13 Feb 2019 14:21:50 GMT https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535191#M6424 novinosrin 2019-02-13T14:21:50Z Re: Remove matching credit and debit entries in a dataset https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535215#M6428 <P>yes, that transaction should stay. error from my end</P> Wed, 13 Feb 2019 15:13:27 GMT https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535215#M6428 sivakoya 2019-02-13T15:13:27Z Re: Remove matching credit and debit entries in a dataset https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535229#M6429 <P>Hi&nbsp; <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/31142">@sivakoya</a>&nbsp; see if this helps</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">data have; input LOAN_NBR INT_AMT_PAID PRIN_AMT_PAID EFF_FROM_DT :date9.; format EFF_FROM_DT date9.; cards; 60016100 20.58 -98.50 07Jan2019 60016101 29.72 -80.28 07Jan2019 60016101 15.19 -94.81 28Jan2019 60016102 52.70 -57.35 16Jan2019 60016102 0.00 -110.05 16Jan2019 60016103 121.08 -235.92 04Jan2019 60016122 41.29 -89.06 17Jan2019 60016122 -41.29 89.06 24Jan2019 60016122 57.27 -73.08 29Jan2019 60016212 29.24 -73.85 09Jan2019 60016212 -29.24 73.85 17Jan2019 60016274 142.96 -186.55 11Jan2019 60016274 -142.96 186.55 21Jan2019 60016326 45.99 -96.08 03Jan2019 60016326 -45.99 96.08 08Jan2019 60016326 57.48 -84.59 10Jan2019 60016468 61.45 -113.64 31Dec2018 60016468 -61.45 113.64 31Dec2018 60016501 17.69 -80.70 15Jan2019 60016501 18.76 -79.63 17Jan2019 60016501 -17.69 80.70 17Jan2019 60016701 18.76 -81.24 08Jan2019 60016701 -18.76 81.24 14Jan2019 60016701 26.48 -73.52 22Jan2019 60016701 3.75 -96.25 29Jan2019 60016760 24.25 -102.86 31Dec2018 60016760 5.15 -121.96 07Jan2019 60016760 -5.15 121.96 14Jan2019 60016760 13.24 -113.87 18Jan2019 60016848 32.63 -77.37 21Dec2018 60016848 50.90 -55.10 04Jan2019 60016848 -32.63 77.37 04Jan2019 60016848 18.04 -87.57 18Jan2019 ; data temp; set have; n1=abs(INT_AMT_PAID); n2=abs(PRIN_AMT_PAID); run; proc sort data=temp out=_temp; by loan_nbr n1 n2 ; run; data want; n=1; do _n_=1 by 1 until(last.loan_nbr); set _temp; by loan_nbr; array t(9999); if sum(INT_AMT_PAID,lag(INT_AMT_PAID))=0 and sum(PRIN_AMT_PAID,lag(PRIN_AMT_PAID))=0 then do; t(n)=_n_-1; n+1; t(n)=_n_; end; end; do _n_=1 by 1 until(last.loan_nbr); set _temp; by loan_nbr; if _n_ not in t then output; end; drop t: n:; run; </CODE></PRE> <P>If you want more robust, I can apply hashes , but we do not know how comfortable you are though, so feel free to let us know</P> Wed, 13 Feb 2019 15:46:18 GMT https://communities.sas.com/t5/New-SAS-User/Remove-matching-credit-and-debit-entries-in-a-dataset/m-p/535229#M6429 novinosrin 2019-02-13T15:46:18Z