DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 13

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

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

.

Occasional Contributor
Posts: 8

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

Posted 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;
Occasional Contributor
Posts: 13

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

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

 

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

PROC Star
Posts: 1,283

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

Posted 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

 

Super User
Posts: 12,994

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

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

Super User
Super User
Posts: 9,193

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

[ Edited ]
Posted 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"?

Ask a Question
Discussion stats
  • 5 replies
  • 138 views
  • 0 likes
  • 5 in conversation