topic Re: Keep record based on condition in SAS Programming https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590245#M168894 <P><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/155169">@d0816</a>:</P> <P>I freely admit that I love the double-DoW-loop solution by&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/462">@PGStats</a>&nbsp;because (a) it aligns with my procedural programming proclivities and (b) one would be hard-pressed to find a more ardent proponent of the DoW-loop than myself. Just looking at the&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/462">@PGStats</a>&nbsp;program, I can understand its logic and what it's doing with the data better than any lengthy explanations.</P> <P>&nbsp;</P> <P>The SQL program below also works to attain the same result (and doesn't require <EM>explicit</EM> sorting to boot - it sorts behind-the-scenes implicitly). However, (a) if I didn't write it myself, understanding what it's doing would be much harder for me compared to the DoW-loop, and (b) it doesn't guarantee that the output will retain the same relative record order.</P> <PRE><CODE class=" language-sas">data have ; input id startdate :mmddyy10. (startmonth yearmonth) (:$7.) ; format startdate yymmdd10. ; cards ; 1 4/8/2019 2019-04 2019-09 1 4/30/2019 2019-04 2019-09 2 4/29/2019 2019-04 2019-07 2 4/29/2019 2019-04 2019-08 2 4/29/2019 2019-04 2019-09 3 6/17/2019 2019-06 2019-05 3 6/17/2019 2019-06 2019-06 3 6/17/2019 2019-06 2019-07 3 6/17/2019 2019-06 2019-08 3 6/17/2019 2019-06 2019-09 3 6/17/2019 2019-06 2019-04 4 4/1/2019 2019-04 2019-03 4 4/1/2019 2019-04 2019-04 4 4/1/2019 2019-04 2019-05 4 4/1/2019 2019-04 2019-06 4 4/1/2019 2019-04 2019-07 4 4/1/2019 2019-04 2019-08 4 4/1/2019 2019-04 2019-09 4 6/5/2019 2019-06 2019-03 4 6/5/2019 2019-06 2019-04 4 6/5/2019 2019-06 2019-05 4 6/5/2019 2019-06 2019-06 4 6/5/2019 2019-06 2019-07 4 6/5/2019 2019-06 2019-08 4 6/5/2019 2019-06 2019-09 4 7/23/2019 2019-07 2019-03 4 7/23/2019 2019-07 2019-04 4 7/23/2019 2019-07 2019-05 4 7/23/2019 2019-07 2019-06 4 7/23/2019 2019-07 2019-07 4 7/23/2019 2019-07 2019-08 4 7/23/2019 2019-07 2019-09 5 5/1/2019 2019-05 2019-09 ; run ; proc sql ; create table want as select * from have where startmonth = yearmonth union select * from have where id not in (select id from have where startmonth = yearmonth) group id having yearmonth = max (yearmonth) ; quit ; </CODE></PRE> <P>It's one of those rare cases when&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/462">@PGStats</a>&nbsp;would present a DATA step solution and I'd offer SQL. But for me, being rather SQL-maladroit, it's a useful exercise.</P> <P>&nbsp;</P> <P>Kind regards</P> <P>Paul D.</P> <P>&nbsp;</P> <P>&nbsp; &nbsp; &nbsp;</P> Fri, 20 Sep 2019 02:00:12 GMT hashman 2019-09-20T02:00:12Z Keep record based on condition https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590179#M168874 <P>Hello,</P><P>I Have following dataset. For&nbsp;an&nbsp;ID&nbsp;<U>AND</U> StartDate, I want to keep the record based on following criteria and order:</P><P>1. Keep a record with matching StartMonth and YearMonth. (For.eg. ID 4)</P><P>2. If matching start month is not available, then keep a record&nbsp;with most recent YearMonth.(For.eg. ID 2)</P><P>3. If matching start month is not available and only one&nbsp;YearMonth is available, then keep the record with that YearMonth (For.eg. ID 5)</P><P>(There are no missing values is all the variable and there are other variables than what is shown here in sample dataset.)</P><P>&nbsp;</P><P>Dataset Have:</P><TABLE><TBODY><TR><TD>ID</TD><TD>StartDate</TD><TD>StartMonth</TD><TD>YearMonth</TD><TD>company</TD><TD>area</TD></TR><TR><TD>1</TD><TD>4/8/2019</TD><TD>2019-04</TD><TD>2019-09</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>1</TD><TD>4/30/2019</TD><TD>2019-04</TD><TD>2019-09</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>2</TD><TD>4/29/2019</TD><TD>2019-04</TD><TD>2019-07</TD><TD>wr</TD><TD>barron</TD></TR><TR><TD>2</TD><TD>4/29/2019</TD><TD>2019-04</TD><TD>2019-08</TD><TD>wr</TD><TD>eau claire</TD></TR><TR><TD>2</TD><TD>4/29/2019</TD><TD>2019-04</TD><TD>2019-09</TD><TD>wr</TD><TD>eau claire</TD></TR><TR><TD>3</TD><TD>6/17/2019</TD><TD>2019-06</TD><TD>2019-05</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>3</TD><TD>6/17/2019</TD><TD>2019-06</TD><TD>2019-06</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>3</TD><TD>6/17/2019</TD><TD>2019-06</TD><TD>2019-07</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>3</TD><TD>6/17/2019</TD><TD>2019-06</TD><TD>2019-08</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>3</TD><TD>6/17/2019</TD><TD>2019-06</TD><TD>2019-09</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>3</TD><TD>6/17/2019</TD><TD>2019-06</TD><TD>2019-04</TD><TD>um</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>4/1/2019</TD><TD>2019-04</TD><TD>2019-03</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>4/1/2019</TD><TD>2019-04</TD><TD>2019-04</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>4/1/2019</TD><TD>2019-04</TD><TD>2019-05</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>4/1/2019</TD><TD>2019-04</TD><TD>2019-06</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>4/1/2019</TD><TD>2019-04</TD><TD>2019-07</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>4/1/2019</TD><TD>2019-04</TD><TD>2019-08</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>4/1/2019</TD><TD>2019-04</TD><TD>2019-09</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>6/5/2019</TD><TD>2019-06</TD><TD>2019-03</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>6/5/2019</TD><TD>2019-06</TD><TD>2019-04</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>6/5/2019</TD><TD>2019-06</TD><TD>2019-05</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>6/5/2019</TD><TD>2019-06</TD><TD>2019-06</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>6/5/2019</TD><TD>2019-06</TD><TD>2019-07</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>6/5/2019</TD><TD>2019-06</TD><TD>2019-08</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>6/5/2019</TD><TD>2019-06</TD><TD>2019-09</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>7/23/2019</TD><TD>2019-07</TD><TD>2019-03</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>7/23/2019</TD><TD>2019-07</TD><TD>2019-04</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>7/23/2019</TD><TD>2019-07</TD><TD>2019-05</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>7/23/2019</TD><TD>2019-07</TD><TD>2019-06</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>7/23/2019</TD><TD>2019-07</TD><TD>2019-07</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>7/23/2019</TD><TD>2019-07</TD><TD>2019-08</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>7/23/2019</TD><TD>2019-07</TD><TD>2019-09</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>5</TD><TD>5/1/2019</TD><TD>2019-05</TD><TD>2019-09</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>6</TD><TD>6/3/2019</TD><TD>2019-06</TD><TD>2019-04</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>6</TD><TD>6/3/2019</TD><TD>2019-06</TD><TD>2019-05</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>6</TD><TD>6/3/2019</TD><TD>2019-06</TD><TD>2019-06</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>6</TD><TD>6/3/2019</TD><TD>2019-06</TD><TD>2019-07</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>6</TD><TD>6/3/2019</TD><TD>2019-06</TD><TD>2019-08</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>6</TD><TD>6/3/2019</TD><TD>2019-06</TD><TD>2019-09</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>7</TD><TD>5/2/2019</TD><TD>2019-05</TD><TD>2019-03</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/2/2019</TD><TD>2019-05</TD><TD>2019-04</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/2/2019</TD><TD>2019-05</TD><TD>2019-05</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/2/2019</TD><TD>2019-05</TD><TD>2019-06</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/2/2019</TD><TD>2019-05</TD><TD>2019-07</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/2/2019</TD><TD>2019-05</TD><TD>2019-08</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/2/2019</TD><TD>2019-05</TD><TD>2019-09</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/25/2019</TD><TD>2019-05</TD><TD>2019-03</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/25/2019</TD><TD>2019-05</TD><TD>2019-04</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/25/2019</TD><TD>2019-05</TD><TD>2019-05</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/25/2019</TD><TD>2019-05</TD><TD>2019-06</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/25/2019</TD><TD>2019-05</TD><TD>2019-07</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/25/2019</TD><TD>2019-05</TD><TD>2019-08</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/25/2019</TD><TD>2019-05</TD><TD>2019-09</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>7/15/2019</TD><TD>2019-07</TD><TD>2019-03</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>7/15/2019</TD><TD>2019-07</TD><TD>2019-04</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>7/15/2019</TD><TD>2019-07</TD><TD>2019-05</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>7/15/2019</TD><TD>2019-07</TD><TD>2019-06</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>7/15/2019</TD><TD>2019-07</TD><TD>2019-07</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>7/15/2019</TD><TD>2019-07</TD><TD>2019-08</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>7/15/2019</TD><TD>2019-07</TD><TD>2019-09</TD><TD>fs</TD><TD>door</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Dataset Want:</P><TABLE><TBODY><TR><TD>ID</TD><TD>StartDate</TD><TD>StartMonth</TD><TD>YearMonth</TD><TD>company</TD><TD>area</TD></TR><TR><TD>1</TD><TD>4/8/2019</TD><TD>2019-04</TD><TD>2019-09</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>1</TD><TD>4/30/2019</TD><TD>2019-04</TD><TD>2019-09</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>2</TD><TD>4/29/2019</TD><TD>2019-04</TD><TD>2019-09</TD><TD>wr</TD><TD>eau claire</TD></TR><TR><TD>3</TD><TD>6/17/2019</TD><TD>2019-06</TD><TD>2019-06</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>4/1/2019</TD><TD>2019-04</TD><TD>2019-04</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>6/5/2019</TD><TD>2019-06</TD><TD>2019-06</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>4</TD><TD>7/23/2019</TD><TD>2019-07</TD><TD>2019-07</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>5</TD><TD>5/1/2019</TD><TD>2019-05</TD><TD>2019-09</TD><TD>aw</TD><TD>mke</TD></TR><TR><TD>6</TD><TD>6/3/2019</TD><TD>2019-06</TD><TD>2019-06</TD><TD>ro</TD><TD>mke</TD></TR><TR><TD>7</TD><TD>5/2/2019</TD><TD>2019-05</TD><TD>2019-05</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>5/25/2019</TD><TD>2019-05</TD><TD>2019-05</TD><TD>fs</TD><TD>door</TD></TR><TR><TD>7</TD><TD>7/15/2019</TD><TD>2019-07</TD><TD>2019-07</TD><TD>fs</TD><TD>door</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Appreciate any help.</P><P>&nbsp;</P><P>Thank you.</P> Thu, 19 Sep 2019 20:01:54 GMT https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590179#M168874 d0816 2019-09-19T20:01:54Z Re: Keep record based on condition https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590225#M168881 <P>This should do it:</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">data want; do until(last.StartDate); set have; by id StartDate; if startMonth=yearMonth then match = 1; if yearMonth &gt; mrym then mrym = yearMonth; end; do until(last.StartDate); set have; by id StartDate; if match then do; if startMonth=yearMonth then output; end; else if yearMonth = mrym then output; end; drop match mrym; run;</CODE></PRE> Thu, 19 Sep 2019 22:20:46 GMT https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590225#M168881 PGStats 2019-09-19T22:20:46Z Re: Keep record based on condition https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590245#M168894 <P><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/155169">@d0816</a>:</P> <P>I freely admit that I love the double-DoW-loop solution by&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/462">@PGStats</a>&nbsp;because (a) it aligns with my procedural programming proclivities and (b) one would be hard-pressed to find a more ardent proponent of the DoW-loop than myself. Just looking at the&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/462">@PGStats</a>&nbsp;program, I can understand its logic and what it's doing with the data better than any lengthy explanations.</P> <P>&nbsp;</P> <P>The SQL program below also works to attain the same result (and doesn't require <EM>explicit</EM> sorting to boot - it sorts behind-the-scenes implicitly). However, (a) if I didn't write it myself, understanding what it's doing would be much harder for me compared to the DoW-loop, and (b) it doesn't guarantee that the output will retain the same relative record order.</P> <PRE><CODE class=" language-sas">data have ; input id startdate :mmddyy10. (startmonth yearmonth) (:$7.) ; format startdate yymmdd10. ; cards ; 1 4/8/2019 2019-04 2019-09 1 4/30/2019 2019-04 2019-09 2 4/29/2019 2019-04 2019-07 2 4/29/2019 2019-04 2019-08 2 4/29/2019 2019-04 2019-09 3 6/17/2019 2019-06 2019-05 3 6/17/2019 2019-06 2019-06 3 6/17/2019 2019-06 2019-07 3 6/17/2019 2019-06 2019-08 3 6/17/2019 2019-06 2019-09 3 6/17/2019 2019-06 2019-04 4 4/1/2019 2019-04 2019-03 4 4/1/2019 2019-04 2019-04 4 4/1/2019 2019-04 2019-05 4 4/1/2019 2019-04 2019-06 4 4/1/2019 2019-04 2019-07 4 4/1/2019 2019-04 2019-08 4 4/1/2019 2019-04 2019-09 4 6/5/2019 2019-06 2019-03 4 6/5/2019 2019-06 2019-04 4 6/5/2019 2019-06 2019-05 4 6/5/2019 2019-06 2019-06 4 6/5/2019 2019-06 2019-07 4 6/5/2019 2019-06 2019-08 4 6/5/2019 2019-06 2019-09 4 7/23/2019 2019-07 2019-03 4 7/23/2019 2019-07 2019-04 4 7/23/2019 2019-07 2019-05 4 7/23/2019 2019-07 2019-06 4 7/23/2019 2019-07 2019-07 4 7/23/2019 2019-07 2019-08 4 7/23/2019 2019-07 2019-09 5 5/1/2019 2019-05 2019-09 ; run ; proc sql ; create table want as select * from have where startmonth = yearmonth union select * from have where id not in (select id from have where startmonth = yearmonth) group id having yearmonth = max (yearmonth) ; quit ; </CODE></PRE> <P>It's one of those rare cases when&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/462">@PGStats</a>&nbsp;would present a DATA step solution and I'd offer SQL. But for me, being rather SQL-maladroit, it's a useful exercise.</P> <P>&nbsp;</P> <P>Kind regards</P> <P>Paul D.</P> <P>&nbsp;</P> <P>&nbsp; &nbsp; &nbsp;</P> Fri, 20 Sep 2019 02:00:12 GMT https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590245#M168894 hashman 2019-09-20T02:00:12Z Re: Keep record based on condition https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590427#M168978 <P><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/21262">@hashman</a>: Always interesting to see different approaches. The sample data provided by the OP are a bit tricky in that they don't contain all possible cases. (Of course, I don't know what is really "possible" in that context.) With two small changes -- <FONT face="courier new,courier">yearmonth='2019-0<STRONG><FONT color="#FF0000">8</FONT></STRONG>'</FONT>&nbsp;(instead of <FONT face="courier new,courier">-0<STRONG>9</STRONG></FONT>) in the first observation and&nbsp;<FONT face="courier new,courier">yearmonth='2019-0<STRONG><FONT color="#FF0000">2</FONT></STRONG>'</FONT>&nbsp;(instead of <FONT face="courier new,courier">-0<STRONG>4</STRONG></FONT>) in the 13th (that is for <FONT face="courier new,courier">ID 4</FONT>, <FONT face="courier new,courier">startdate 4/1/2019</FONT>) -- I'm afraid your PROC SQL step would fail to select an observation for the two corresponding "ID-STARTDATE BY groups" because for the second table of the "union" only ID is used to identify groups of observations.</P> <P>&nbsp;</P> <P>A simple fix (for that second table) would be</P> <PRE> select * from have where <STRONG>catx(',',id,startdate)</STRONG> not in (select <STRONG>catx(',',id,startdate)</STRONG> from have where startmonth = yearmonth) group id<STRONG>, startdate</STRONG> having yearmonth = max (yearmonth)</PRE> <P>Another option:</P> <PRE><CODE class=" language-sas"> select a.* from have a left join have(where=(startmonth=yearmonth)) b on a.id=b.id &amp; a.startdate=b.startdate where b.id=. group a.id, a.startdate having a.yearmonth = max (a.yearmonth)</CODE></PRE> <P>In a test with a larger HAVE dataset (~300,000 obs., WANT ~71,000 obs.) these were equally fast (but slower than the DATA step), whereas a more concise correlated subquery (<FONT face="courier new,courier">where not exists (...)</FONT>) was extremely slow.</P> Fri, 20 Sep 2019 14:50:18 GMT https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590427#M168978 FreelanceReinh 2019-09-20T14:50:18Z Re: Keep record based on condition https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590435#M168980 <P><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/462">@PGStats</a>&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/21262">@hashman</a>&nbsp;and <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/32733">@FreelanceReinh</a>,</P><P>Thank you all&nbsp;so much for responding.</P><P>The codes by <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/462">@PGStats</a>&nbsp;and <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/21262">@hashman</a>&nbsp;both worked in the sample dataset I provided. But two codes returned me with different number of observations in my real dataset.&nbsp; I compared the results of two codes in my real dataset and&nbsp;looked for&nbsp;IDs with different no. of records. Like <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/32733">@FreelanceReinh</a>&nbsp;said,&nbsp;my sample dataset&nbsp;may not have&nbsp;contained&nbsp;all possible cases even though I strived for it. So I created new sample dataset (see below) from original dataset. Data step code is returning the result as intended. It looks like SQL code is picking most recent StartDate only for an ID. I will try the fix <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/32733">@FreelanceReinh</a>&nbsp;suggested. Thanks.</P><P>&nbsp;</P><P><FONT color="#000080" face="Courier New" size="2"><STRONG>data</STRONG></FONT><FONT face="Courier New" size="2"> have_new ; </FONT></P><P><FONT color="#0000ff" face="Courier New" size="2">input</FONT><FONT face="Courier New" size="2"> id startdate :</FONT><FONT color="#008080" face="Courier New" size="2">mmddyy10.</FONT><FONT face="Courier New" size="2"> (startmonth yearmonth) (:</FONT><FONT color="#008080" face="Courier New" size="2">$7.</FONT><FONT face="Courier New" size="2">) ; </FONT></P><P><FONT color="#0000ff" face="Courier New" size="2">format</FONT><FONT face="Courier New" size="2"> startdate </FONT><FONT color="#008080" face="Courier New" size="2">yymmdd10.</FONT><FONT face="Courier New" size="2"> ; </FONT></P><P><FONT color="#0000ff" face="Courier New" size="2">cards</FONT><FONT face="Courier New" size="2"> ; </FONT></P><P>100 5/19/2019 2019-05 2019-06</P><P>100 5/19/2019 2019-05 2019-07</P><P>100 5/19/2019 2019-05 2019-08</P><P>100 5/19/2019 2019-05 2019-09</P><P>100 7/27/2019 2019-07 2019-06</P><P>100 7/27/2019 2019-07 2019-07</P><P>100 7/27/2019 2019-07 2019-08</P><P>100 7/27/2019 2019-07 2019-09</P><P>200 5/6/2019 2019-05 2019-06</P><P>200 5/6/2019 2019-05 2019-07</P><P>200 5/6/2019 2019-05 2019-08</P><P>200 5/6/2019 2019-05 2019-09</P><P>200 6/6/2019 2019-06 2019-06</P><P>200 6/6/2019 2019-06 2019-07</P><P>200 6/6/2019 2019-06 2019-08</P><P>200 6/6/2019 2019-06 2019-09</P><P>200 7/17/2019 2019-07 2019-06</P><P>200 7/17/2019 2019-07 2019-07</P><P>200 7/17/2019 2019-07 2019-08</P><P>200 7/17/2019 2019-07 2019-09</P><P>300 4/15/2019 2019-04 2019-07</P><P>300 4/15/2019 2019-04 2019-08</P><P>300 4/15/2019 2019-04 2019-09</P><P>300 7/24/2019 2019-07 2019-07</P><P>300 7/24/2019 2019-07 2019-08</P><P>300 7/24/2019 2019-07 2019-09</P><P>400 5/7/2019 2019-05 2019-06</P><P>400 5/7/2019 2019-05 2019-07</P><P>400 5/7/2019 2019-05 2019-08</P><P>400 5/7/2019 2019-05 2019-09</P><P>400 7/25/2019 2019-07 2019-06</P><P>400 7/25/2019 2019-07 2019-07</P><P>400 7/25/2019 2019-07 2019-08</P><P>400 7/25/2019 2019-07 2019-09</P><P>500 4/15/2019 2019-04 2019-05</P><P>500 4/15/2019 2019-04 2019-06</P><P>500 4/15/2019 2019-04 2019-07</P><P>500 4/15/2019 2019-04 2019-08</P><P>500 4/15/2019 2019-04 2019-09</P><P>500 6/12/2019 2019-06 2019-05</P><P>500 6/12/2019 2019-06 2019-06</P><P>500 6/12/2019 2019-06 2019-07</P><P>500 6/12/2019 2019-06 2019-08</P><P>500 6/12/2019 2019-06 2019-09</P><P>600 4/1/2019 2019-04 2019-07</P><P>600 4/1/2019 2019-04 2019-08</P><P>600 4/1/2019 2019-04 2019-09</P><P>600 7/29/2019 2019-07 2019-07</P><P>600 7/29/2019 2019-07 2019-08</P><P>600 7/29/2019 2019-07 2019-09</P><P>700 3/15/2019 2019-03 2019-05</P><P>700 3/15/2019 2019-03 2019-06</P><P>700 3/15/2019 2019-03 2019-07</P><P>700 3/15/2019 2019-03 2019-08</P><P>700 3/15/2019 2019-03 2019-09</P><P>700 6/14/2019 2019-06 2019-05</P><P>700 6/14/2019 2019-06 2019-06</P><P>700 6/14/2019 2019-06 2019-07</P><P>700 6/14/2019 2019-06 2019-08</P><P>700 6/14/2019 2019-06 2019-09</P><P>800 4/17/2019 2019-04 2019-07</P><P>800 4/17/2019 2019-04 2019-08</P><P>800 4/17/2019 2019-04 2019-09</P><P>800 7/10/2019 2019-07 2019-07</P><P>800 7/10/2019 2019-07 2019-08</P><P>800 7/10/2019 2019-07 2019-09</P><P>900 5/21/2019 2019-05 2019-07</P><P>900 5/21/2019 2019-05 2019-08</P><P>900 5/21/2019 2019-05 2019-09</P><P>900 7/31/2019 2019-07 2019-07</P><P>900 7/31/2019 2019-07 2019-08</P><P>900 7/31/2019 2019-07 2019-09</P><P>1000 6/17/2019 2019-06 2019-07</P><P>1000 6/17/2019 2019-06 2019-08</P><P>1000 6/17/2019 2019-06 2019-09</P><P>1000 7/12/2019 2019-07 2019-07</P><P>1000 7/12/2019 2019-07 2019-08</P><P>1000 7/12/2019 2019-07 2019-09</P><P>2000 3/12/2019 2019-03 2019-07</P><P>2000 3/12/2019 2019-03 2019-08</P><P>2000 3/12/2019 2019-03 2019-09</P><P>2000 7/29/2019 2019-07 2019-07</P><P>2000 7/29/2019 2019-07 2019-08</P><P>2000 7/29/2019 2019-07 2019-09</P><P>3000 3/18/2019 2019-03 2019-04</P><P>3000 3/18/2019 2019-03 2019-05</P><P>3000 3/18/2019 2019-03 2019-06</P><P>3000 3/18/2019 2019-03 2019-07</P><P>3000 3/18/2019 2019-03 2019-08</P><P>3000 3/18/2019 2019-03 2019-09</P><P>3000 5/9/2019 2019-05 2019-04</P><P>3000 5/9/2019 2019-05 2019-05</P><P>3000 5/9/2019 2019-05 2019-06</P><P>3000 5/9/2019 2019-05 2019-07</P><P>3000 5/9/2019 2019-05 2019-08</P><P>3000 5/9/2019 2019-05 2019-09</P><P>4000 3/23/2019 2019-03 2019-06</P><P>4000 3/23/2019 2019-03 2019-07</P><P>4000 3/23/2019 2019-03 2019-08</P><P>4000 3/23/2019 2019-03 2019-09</P><P>4000 7/13/2019 2019-07 2019-06</P><P>4000 7/13/2019 2019-07 2019-07</P><P>4000 7/13/2019 2019-07 2019-08</P><P>4000 7/13/2019 2019-07 2019-09</P><P>5000 4/1/2019 2019-04 2019-05</P><P>5000 4/1/2019 2019-04 2019-06</P><P>5000 4/1/2019 2019-04 2019-07</P><P>5000 4/1/2019 2019-04 2019-08</P><P>5000 4/1/2019 2019-04 2019-09</P><P>5000 6/26/2019 2019-06 2019-05</P><P>5000 6/26/2019 2019-06 2019-06</P><P>5000 6/26/2019 2019-06 2019-07</P><P>5000 6/26/2019 2019-06 2019-08</P><P>5000 6/26/2019 2019-06 2019-09</P><P>6000 3/23/2019 2019-03 2019-06</P><P>6000 3/23/2019 2019-03 2019-07</P><P>6000 3/23/2019 2019-03 2019-08</P><P>6000 3/23/2019 2019-03 2019-09</P><P>6000 4/16/2019 2019-04 2019-06</P><P>6000 4/16/2019 2019-04 2019-07</P><P>6000 4/16/2019 2019-04 2019-08</P><P>6000 4/16/2019 2019-04 2019-09</P><P>6000 7/2/2019 2019-07 2019-06</P><P>6000 7/2/2019 2019-07 2019-07</P><P>6000 7/2/2019 2019-07 2019-08</P><P>6000 7/2/2019 2019-07 2019-09</P><P>7000 3/8/2019 2019-03 2019-05</P><P>7000 3/8/2019 2019-03 2019-06</P><P>7000 3/8/2019 2019-03 2019-07</P><P>7000 3/8/2019 2019-03 2019-08</P><P>7000 3/8/2019 2019-03 2019-09</P><P>7000 7/2/2019 2019-07 2019-05</P><P>7000 7/2/2019 2019-07 2019-06</P><P>7000 7/2/2019 2019-07 2019-07</P><P>7000 7/2/2019 2019-07 2019-08</P><P>7000 7/2/2019 2019-07 2019-09</P><P><FONT face="Courier New" size="2">; </FONT><STRONG><FONT color="#000080" face="Courier New" size="2">run</FONT></STRONG><FONT face="Courier New" size="2">;</FONT></P><P>&nbsp;</P> Fri, 20 Sep 2019 15:13:50 GMT https://communities.sas.com/t5/SAS-Programming/Keep-record-based-on-condition/m-p/590435#M168980 d0816 2019-09-20T15:13:50Z