Hi Mark and Linus the problem is that the id in the second table.is different to that of the first i.e. PERIOD_IDENTIFIER YEAR PERIOD GROUP 1111 2016 PERIOD 1 G '10Jan2016'd '28Jan2016'd 1112 2016 PERIOD 1 H '10Jan2016'd '28Jan2016'd 1113 2016 PERIOD 2 G '04Jul2016'd '19Jul2016'd 1114 2016 PERIOD 2 H '04Jul2016'd '19Jul2016'd 1115 2016 PERIOD 3 G '26Sep2016'd '11Oct2016'd 1116 2016 PERIOD 3 H '26Sep2016'd '11Oct2016'd 1117 2016 PERIOD 4 G '18Dec2016'd '31Jan2017'd 1118 2016 PERIOD 4 H '19Dec2016'd '28Jan2017'd I mean we can try to append all the date that are between the date range with the PERIOD_IDENTIFIER appended to the first (transaction) table but we first need to join these 2 tables somehow. The first tables will have duplicates but the second table the identifier is unique. The WANT data set look something like this id transaction_date group PERIOD_START PERIOD_END FLAG 1 19/03/2016 G . . 0 2 21/01/2016 H '10Jan2016'd '28Jan2016'd 1 3 05/07/2016 H '04Jul2016'd '19Jul2016'd 1 4 27/09/2016 G '26Sep2016'd '11Oct2016'd 1 5 25/03/2016 G . . 0 6 02/01/2017 H '19Dec2016'd '28Jan2017'd 1 So far I have attempted the following codes which returns duplicates proc sql; create table test as select a.id , a.transaction_date , a.group , b.period_start , b.period_end , flag from transaction_table a left outer join period_table b on a.transaction_date between b.period_start and b.period_end and a.group = b.group quit; Unless I'm missing out something or proc sql might not be good for this task.
... View more