Dates with case statement to get flag

[vickys]

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

[RW9]

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