DATA Step, Macro, Functions and more

Code to pick the latest emply record based on review date

Reply
Occasional Contributor
Posts: 15

Code to pick the latest emply record based on review date

Hi Guys,

 

I have attached belwo sample data with two secnarios..i need help in wiritng a sas code to pick the latest record by comparing the Review date that falls between EFFT_D(Joing date) and EXPY_D(end date).

 

Any help is much appreciated.

n first scenario i should pick the belowrecord

10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838930051CLI01342327Oct201631Dec2099MAY2018OCT2016

DEC2099

 

 

 

in second scenario the below lrecord

 

10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838930049CLI01342302Mar201511Apr2018MAY2018MAR2015APR2018

 

 complete data below...

 

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,YYYACT2002982820012838930049CLI01342302Mar201526Oct2016MAY2018MAR2015OCT2016
10Apr2018XXXYYYAPR2018XXX,YYY00183256XXX,YYYACT2002982820012838930051CLI01342327Oct201631Dec2099MAY2018OCT2016DEC2099
                 
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
Super Contributor
Super Contributor
Posts: 265

Re: Code to pick the latest emply record based on review date

Posted in reply to BaalaRaaji

Next time, trim your data set down to just the essential fields.

 

data first_set;
  input 
Review_Date:date9.	
Name:$6.
Reviewmonth:MONYY7.	
RO:$8.
EMPL_I:$8.	
EMPL_M:$8.	
EMPL_STUS_C:$4.	
POSN_I:$9.	
PARN_POSN_I:$9.	
JOB_C:$9.	
JOB_FUNC_C:$7.	
DEPT_I:$7.	
EFFT_D:date9.	
EXPY_D:date9.	
appmonth:MONYY7.	
Joinmthyy:MONYY7.	
Endmthyy:MONYY7.
;
format review_date yymmdd10. efft_d yymmdd10. expy_d yymmdd10.;

datalines;
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20012841	20012872	900802	CSL	005513	31Jul2000	09Jun2003	MAY2018	JUL2000	JUN2003
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20012841	20012872	902312	LND	005513	10Jun2003	23Nov2003	MAY2018	JUN2003	NOV2003
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20012841	20012872	901459	IB	005513	24Nov2003	07Jan2004	MAY2018	NOV2003	JAN2004
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029525	.         	900661	OTH	012113	08Jan2004	09Jan2005	MAY2018	JAN2004	JAN2005
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20012841	20012872	901459	IB	005513	10Jan2005	07Mar2005	MAY2018	JAN2005	MAR2005
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20012841	20026356	901459	IB	005513	08Mar2005	08Mar2005	MAY2018	MAR2005	MAR2005
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20012841	20012872	901459	IB	005513	09Mar2005	04Aug2005	MAY2018	MAR2005	AUG2005
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012847	904956	CSL	005513	05Aug2005	06Nov2005	MAY2018	AUG2005	NOV2005
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012847	903755	CSL	005513	07Nov2005	29Dec2005	MAY2018	NOV2005	DEC2005
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012838	903755	CSL	005513	30Dec2005	18Jul2006	MAY2018	DEC2005	JUL2006
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012847	903755	CSL	013423	19Jul2006	31Oct2006	MAY2018	JUL2006	OCT2006
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012838	903755	CSL	013423	01Nov2006	29Apr2007	MAY2018	NOV2006	APR2007
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20064500	903755	CSL	013423	30Apr2007	01May2007	MAY2018	APR2007	MAY2007
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012838	903755	CSL	013423	02May2007	14Sep2008	MAY2018	MAY2007	SEP2008
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20036820	. 			900661	OTH	012255	15Sep2008	07May2009	MAY2018	SEP2008	MAY2009
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	LWP	20036820	. 			900661	OTH	012255	08May2009	13Sep2009	MAY2018	MAY2009	SEP2009
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20036821	. 			900659	OTH	012255	14Sep2009	31Dec2009	MAY2018	SEP2009	DEC2009
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012838	903755	CSL	013423	01Jan2010	22Jun2011	MAY2018	JAN2010	JUN2011
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012838	930049	CLI	013423	23Jun2011	16Oct2011	MAY2018	JUN2011	OCT2011
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012838	930049	CLI	013423	17Oct2011	03Mar2013	MAY2018	OCT2011	MAR2013
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	LWP	20036820	. 			930527	OTH	012255	04Mar2013	01Mar2015	MAY2018	MAR2013	MAR2015
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012838	930049	CLI	013423	02Mar2015	26Oct2016	MAY2018	MAR2015	OCT2016
10Apr2018	XXXYYY	APR2018	XXX,YYY	00183256	XXX,YYY	ACT	20029828	20012838	930051	CLI	013423	27Oct2016	31Dec2099	MAY2018	OCT2016	DEC2099
;
run;

* we want the record with a review date between efft_d date and expy_d date;

proc sql;
 	select * 
	from first_set
	where review_date between efft_d and expy_d;
quit;

That gives:

The SAS System          09:52 Thursday, May 3, 2018   1

   Review_                                                     EMPL_                 PARN_POSN_
      Date  Name    Reviewmonth  RO        EMPL_I    EMPL_M    STUS_C     POSN_I     I
           JOB_FUNC_
JOB_C      C          DEPT_I       EFFT_D      EXPY_D  appmonth  Joinmthyy  Endmthyy

2018-04-10  XXXYYY        21275  XXX,YYY   00183256  XXX,YYY   ACT        20029828   20012838
930051     CLI        013423   2016-10-27  2099-12-31     21305      20728     51104


Yuck that is hard to read. 

 

Try that.

Ask a Question
Discussion stats
  • 1 reply
  • 26 views
  • 0 likes
  • 2 in conversation