Dates with case statement to get flag

vickys — Thu, 13 Sep 2018 06:35:29 GMT

Hi Techies,

I came across a requirement which is taking a lot of time for me. Could you please help.
I have two data sets DS1 and DS2.

DS1:

EMP_ID role DT WORK_ST_DT WORK_EN_DT
236107 1 31/01/2012 02/01/2013 01/01/2014
236107 2 25/03/2014 02/04/2014 05/06/2014
236107 3 31/12/2014 31/01/2014 30/06/2014

DS2:

EMP_ID ST_DT END_DT IND
236017 01/01/2013 31/06/2013 Y
263107 20/02/2014 30/03/2014 N

1) My first requirement is to get the indicator from DS2 when ds1.DT is between dates DS2.ST_DT and DS2.END_DT,
if DS1.DT is not in a range(role 1 in this case ) then I have to go with DS1.WORK_ST_DT lies between DS2.ST_DT and DS2.END_DT and pick the indicator ( in this case rol 1 gets Y ).

2) If DS1.DT and DS1.WORK_ST_DT ARE not in any range then I have to select the previous indicator.
ex: the role 3 DS1.DT and DS1.WORK_ST_DT ARE NOT not in a range for DS2.ST_DT and DS2.END_DT then I have to get Y Indicator, which is the previous indicator.

When these two tables are joined there is where condition DS1.EMP_ID = DS2.EMP_ID AND DS1.DT LIES between DS2.ST_DT and DS2.END_DT. This is the existing code on top of that I have to make the chnages.

I have tried to give more information as I can, sorry if I make you confused.

Thanks,
Vickys

Re: Dates with case statement to get flag

RW9 — Thu, 13 Sep 2018 08:12:40 GMT

Yes, that is confusing. Please start by posting test data in the form of a datastep, so that we can run it and have some data to play with. Then show what the output should look like.

I am going to take a stab at:

Do an SQL statment which merge the data per 1)

This will give you a dataset with all the information and additional variables.

Next do a datastep and if those variables are present ue that, other lag() the previous result in

topic Dates with case statement to get flag in SAS Programming

Dates with case statement to get flag

Re: Dates with case statement to get flag