cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
BaalaRaaji
Quartz | Level 8 BaalaRaaji
Quartz | Level 8

pick records that fall between start and end date that lies between review date

Posted 05-02-2018 09:26 PM (980 views)

Hi Guys,

 

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..

 

Any help much appreciated

 

 

i need only below record to pick from below data..

 

10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838930049CLI01342302Mar201511Apr2018MAY2018MAR2015APR2018

 

please check below sample data..

Review_DateNameReviewmonthROEMPL_IEMPL_MEMPL_STUS_CPOSN_IPARN_POSN_IJOB_CJOB_FUNC_CDEPT_IEFFT_DEXPY_DappmonthJoinmthyyEndmthyy
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2001284120012872900802CSL00551331Jul200009Jun2003MAY2018JUL2000JUN2003
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2001284120012872902312LND00551310Jun200323Nov2003MAY2018JUN2003NOV2003
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2001284120012872901459IB00551324Nov200307Jan2004MAY2018NOV2003JAN2004
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT20029525 900661OTH01211308Jan200409Jan2005MAY2018JAN2004JAN2005
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2001284120012872901459IB00551310Jan200507Mar2005MAY2018JAN2005MAR2005
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2001284120026356901459IB00551308Mar200508Mar2005MAY2018MAR2005MAR2005
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2001284120012872901459IB00551309Mar200504Aug2005MAY2018MAR2005AUG2005
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012847904956CSL00551305Aug200506Nov2005MAY2018AUG2005NOV2005
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012847903755CSL00551307Nov200529Dec2005MAY2018NOV2005DEC2005
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838903755CSL00551330Dec200518Jul2006MAY2018DEC2005JUL2006
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012847903755CSL01342319Jul200631Oct2006MAY2018JUL2006OCT2006
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838903755CSL01342301Nov200629Apr2007MAY2018NOV2006APR2007
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820064500903755CSL01342330Apr200701May2007MAY2018APR2007MAY2007
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838903755CSL01342302May200714Sep2008MAY2018MAY2007SEP2008
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT20036820 900661OTH01225515Sep200807May2009MAY2018SEP2008MAY2009
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYLWP20036820 900661OTH01225508May200913Sep2009MAY2018MAY2009SEP2009
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT20036821 900659OTH01225514Sep200931Dec2009MAY2018SEP2009DEC2009
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838903755CSL01342301Jan201022Jun2011MAY2018JAN2010JUN2011
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838930049CLI01342323Jun201116Oct2011MAY2018JUN2011OCT2011
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838930049CLI01342317Oct201103Mar2013MAY2018OCT2011MAR2013
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYLWP20036820 930527OTH01225504Mar201301Mar2015MAY2018MAR2013MAR2015
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838930049CLI01342302Mar201511Apr2018MAY2018MAR2015APR2018
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838930051CLI01342312Apr201831Dec2099MAY2018APR2018DEC2099

.

0 Likes
Reply
5 REPLIES 5
JChambo
Fluorite | Level 6 JChambo
Fluorite | Level 6

Re: pick records that fall between start and end date that lies between review date

Posted 05-02-2018 09:56 PM (987 views)  |   In reply to BaalaRaaji

This should just be a straightforward where condition. Does something like the below not work?

 

where EFFT_D <= Review_Date <= EXPY_D;
0 Likes
Reply
BaalaRaaji
Quartz | Level 8 BaalaRaaji
Quartz | Level 8

Re: pick records that fall between start and end date that lies between review date

Posted 05-02-2018 11:08 PM (937 views)  |   In reply to JChambo

No its not working..it did tried this method...and also below one..

 

WHERE max(EFFT_D) <= Review_date <= max(EXPY_D);

0 Likes
Reply
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: pick records that fall between start and end date that lies between review date

Posted 05-02-2018 11:34 PM (927 views)  |   In reply to BaalaRaaji

can you post a sample of your expected output for an/your input sample? ?This will help thr community to offer you precise solutions

 

0 Likes
Reply
ballardw
Super User ballardw
Super User

Re: pick records that fall between start and end date that lies between review date

Posted 05-03-2018 01:09 PM (870 views)  |   In reply to BaalaRaaji

Are your "dates" SAS date values? Comparisons seldom work when the values are character variables.

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.

 

@BaalaRaaji wrote:

No its not working..it did tried this method...and also below one..

 

WHERE max(EFFT_D) <= Review_date <= max(EXPY_D);

"Its not working" is awful vague.

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.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... 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.

0 Likes
Reply
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Re: pick records that fall between start and end date that lies between review date

Posted 05-03-2018 04:19 AM (895 views)  |   In reply to BaalaRaaji

Post test data in the form of a datastep, if you don't know how follow this post:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

Only a few rows are needed (i.e. keep it simple and to the point).  Then from that show what you want out at the end.

@JChambo's suggestion should work.  Your code:

WHERE max(EFFT_D) <= Review_date <= max(EXPY_D);

 

You don't even show if this is SQL or datastep?  How are we to know what "did not work"?

0 Likes
Reply

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • ‎05-02-2018 09:26 PM
  • 981 views
  • 0 likes
  • 5 in conversation