topic Re: pick records that fall between start and end date that lies between review date in SAS Programming https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459533#M116733 <P>can you post a sample of your expected output for an/your input sample? ?This will help thr community to offer you precise solutions</P><P>&nbsp;</P> Thu, 03 May 2018 03:34:31 GMT novinosrin 2018-05-03T03:34:31Z pick records that fall between start and end date that lies between review date https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459525#M116729 <P>Hi Guys,</P><P>&nbsp;</P><P>I want to write a code that picks only one record for each employee based on review date that falls between effective date and expiry date..</P><P>&nbsp;</P><P>Any help much appreciated</P><P>&nbsp;</P><P>&nbsp;</P><P>i need only below record to pick from below data..</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012838</TD><TD>930049</TD><TD>CLI</TD><TD>013423</TD><TD>02Mar2015</TD><TD>11Apr2018</TD><TD>MAY2018</TD><TD>MAR2015</TD><TD>APR2018</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>please check below sample data..</P><TABLE><TBODY><TR><TD>Review_Date</TD><TD>Name</TD><TD>Reviewmonth</TD><TD>RO</TD><TD>EMPL_I</TD><TD>EMPL_M</TD><TD>EMPL_STUS_C</TD><TD>POSN_I</TD><TD>PARN_POSN_I</TD><TD>JOB_C</TD><TD>JOB_FUNC_C</TD><TD>DEPT_I</TD><TD>EFFT_D</TD><TD>EXPY_D</TD><TD>appmonth</TD><TD>Joinmthyy</TD><TD>Endmthyy</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20012841</TD><TD>20012872</TD><TD>900802</TD><TD>CSL</TD><TD>005513</TD><TD>31Jul2000</TD><TD>09Jun2003</TD><TD>MAY2018</TD><TD>JUL2000</TD><TD>JUN2003</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20012841</TD><TD>20012872</TD><TD>902312</TD><TD>LND</TD><TD>005513</TD><TD>10Jun2003</TD><TD>23Nov2003</TD><TD>MAY2018</TD><TD>JUN2003</TD><TD>NOV2003</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20012841</TD><TD>20012872</TD><TD>901459</TD><TD>IB</TD><TD>005513</TD><TD>24Nov2003</TD><TD>07Jan2004</TD><TD>MAY2018</TD><TD>NOV2003</TD><TD>JAN2004</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029525</TD><TD>&nbsp;</TD><TD>900661</TD><TD>OTH</TD><TD>012113</TD><TD>08Jan2004</TD><TD>09Jan2005</TD><TD>MAY2018</TD><TD>JAN2004</TD><TD>JAN2005</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20012841</TD><TD>20012872</TD><TD>901459</TD><TD>IB</TD><TD>005513</TD><TD>10Jan2005</TD><TD>07Mar2005</TD><TD>MAY2018</TD><TD>JAN2005</TD><TD>MAR2005</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20012841</TD><TD>20026356</TD><TD>901459</TD><TD>IB</TD><TD>005513</TD><TD>08Mar2005</TD><TD>08Mar2005</TD><TD>MAY2018</TD><TD>MAR2005</TD><TD>MAR2005</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20012841</TD><TD>20012872</TD><TD>901459</TD><TD>IB</TD><TD>005513</TD><TD>09Mar2005</TD><TD>04Aug2005</TD><TD>MAY2018</TD><TD>MAR2005</TD><TD>AUG2005</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012847</TD><TD>904956</TD><TD>CSL</TD><TD>005513</TD><TD>05Aug2005</TD><TD>06Nov2005</TD><TD>MAY2018</TD><TD>AUG2005</TD><TD>NOV2005</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012847</TD><TD>903755</TD><TD>CSL</TD><TD>005513</TD><TD>07Nov2005</TD><TD>29Dec2005</TD><TD>MAY2018</TD><TD>NOV2005</TD><TD>DEC2005</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012838</TD><TD>903755</TD><TD>CSL</TD><TD>005513</TD><TD>30Dec2005</TD><TD>18Jul2006</TD><TD>MAY2018</TD><TD>DEC2005</TD><TD>JUL2006</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012847</TD><TD>903755</TD><TD>CSL</TD><TD>013423</TD><TD>19Jul2006</TD><TD>31Oct2006</TD><TD>MAY2018</TD><TD>JUL2006</TD><TD>OCT2006</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012838</TD><TD>903755</TD><TD>CSL</TD><TD>013423</TD><TD>01Nov2006</TD><TD>29Apr2007</TD><TD>MAY2018</TD><TD>NOV2006</TD><TD>APR2007</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20064500</TD><TD>903755</TD><TD>CSL</TD><TD>013423</TD><TD>30Apr2007</TD><TD>01May2007</TD><TD>MAY2018</TD><TD>APR2007</TD><TD>MAY2007</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012838</TD><TD>903755</TD><TD>CSL</TD><TD>013423</TD><TD>02May2007</TD><TD>14Sep2008</TD><TD>MAY2018</TD><TD>MAY2007</TD><TD>SEP2008</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20036820</TD><TD>&nbsp;</TD><TD>900661</TD><TD>OTH</TD><TD>012255</TD><TD>15Sep2008</TD><TD>07May2009</TD><TD>MAY2018</TD><TD>SEP2008</TD><TD>MAY2009</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>LWP</TD><TD>20036820</TD><TD>&nbsp;</TD><TD>900661</TD><TD>OTH</TD><TD>012255</TD><TD>08May2009</TD><TD>13Sep2009</TD><TD>MAY2018</TD><TD>MAY2009</TD><TD>SEP2009</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20036821</TD><TD>&nbsp;</TD><TD>900659</TD><TD>OTH</TD><TD>012255</TD><TD>14Sep2009</TD><TD>31Dec2009</TD><TD>MAY2018</TD><TD>SEP2009</TD><TD>DEC2009</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012838</TD><TD>903755</TD><TD>CSL</TD><TD>013423</TD><TD>01Jan2010</TD><TD>22Jun2011</TD><TD>MAY2018</TD><TD>JAN2010</TD><TD>JUN2011</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012838</TD><TD>930049</TD><TD>CLI</TD><TD>013423</TD><TD>23Jun2011</TD><TD>16Oct2011</TD><TD>MAY2018</TD><TD>JUN2011</TD><TD>OCT2011</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012838</TD><TD>930049</TD><TD>CLI</TD><TD>013423</TD><TD>17Oct2011</TD><TD>03Mar2013</TD><TD>MAY2018</TD><TD>OCT2011</TD><TD>MAR2013</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>LWP</TD><TD>20036820</TD><TD>&nbsp;</TD><TD>930527</TD><TD>OTH</TD><TD>012255</TD><TD>04Mar2013</TD><TD>01Mar2015</TD><TD>MAY2018</TD><TD>MAR2013</TD><TD>MAR2015</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012838</TD><TD>930049</TD><TD>CLI</TD><TD>013423</TD><TD>02Mar2015</TD><TD>11Apr2018</TD><TD>MAY2018</TD><TD>MAR2015</TD><TD>APR2018</TD></TR><TR><TD>10Apr2018</TD><TD>XXXYYY</TD><TD>APR2018</TD><TD>XXX,YYY</TD><TD>00183256</TD><TD>XXX,YYY</TD><TD>ACT</TD><TD>20029828</TD><TD>20012838</TD><TD>930051</TD><TD>CLI</TD><TD>013423</TD><TD>12Apr2018</TD><TD>31Dec2099</TD><TD>MAY2018</TD><TD>APR2018</TD><TD>DEC2099</TD></TR></TBODY></TABLE><P>.</P> Thu, 03 May 2018 01:26:48 GMT https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459525#M116729 BaalaRaaji 2018-05-03T01:26:48Z Re: pick records that fall between start and end date that lies between review date https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459529#M116731 <P>This should just be a straightforward where condition. Does something like the below not work?</P><P>&nbsp;</P><PRE><CODE class=" language-sas">where EFFT_D &lt;= Review_Date &lt;= EXPY_D;</CODE></PRE> Thu, 03 May 2018 01:56:50 GMT https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459529#M116731 JChambo 2018-05-03T01:56:50Z Re: pick records that fall between start and end date that lies between review date https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459532#M116732 <P>No its not working..it did tried this method...and also below one..</P><P>&nbsp;</P><P>WHERE max(EFFT_D) &lt;= Review_date &lt;= max(EXPY_D);</P> Thu, 03 May 2018 03:08:39 GMT https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459532#M116732 BaalaRaaji 2018-05-03T03:08:39Z Re: pick records that fall between start and end date that lies between review date https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459533#M116733 <P>can you post a sample of your expected output for an/your input sample? ?This will help thr community to offer you precise solutions</P><P>&nbsp;</P> Thu, 03 May 2018 03:34:31 GMT https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459533#M116733 novinosrin 2018-05-03T03:34:31Z Re: pick records that fall between start and end date that lies between review date https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459563#M116739 <P>Post test data in the form of a datastep, if you don't know how follow this post:</P> <P><A href="https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712" target="_blank">https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712</A></P> <P>&nbsp;</P> <P>Only a few rows are needed (i.e. keep it simple and to the point).&nbsp; Then from that show what you want out at the end.</P> <P><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/207595">@JChambo</a>'s suggestion should work.&nbsp; Your code:</P> <P><SPAN>WHERE max(EFFT_D) &lt;= Review_date &lt;= max(EXPY_D);</SPAN></P> <P>&nbsp;</P> <P><SPAN>You don't even show if this is SQL or datastep?&nbsp; How are we to know what "did not work"?</SPAN></P> Thu, 03 May 2018 08:23:47 GMT https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459563#M116739 RW9 2018-05-03T08:23:47Z Re: pick records that fall between start and end date that lies between review date https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459751#M116801 <P>Are your "dates" SAS date values? Comparisons seldom work when the values are character variables.</P> <P>Run Proc contents to see what type and formats are associated with your data variables. If proc contents shows them as Char then that would be a major reason.</P> <P>&nbsp;</P> <BLOCKQUOTE><HR /><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/44464">@BaalaRaaji</a> wrote:<BR /> <P>No its not working..it did tried this method...and also below one..</P> <P>&nbsp;</P> <P>WHERE max(EFFT_D) &lt;= Review_date &lt;= max(EXPY_D);</P> <HR /></BLOCKQUOTE> <P>"Its not working" is awful vague.<BR /><BR />Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.<BR /><BR />No output? Post any log in a code box.<BR /><BR />Unexpected output? Provide <FONT color="#ff0000">input data in the form of data step code</FONT> pasted into a code box, the actual results and the expected results. Instructions here: <A href="https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat" target="_blank">https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat</A>... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.</P> Thu, 03 May 2018 17:09:03 GMT https://communities.sas.com/t5/SAS-Programming/pick-records-that-fall-between-start-and-end-date-that-lies/m-p/459751#M116801 ballardw 2018-05-03T17:09:03Z