Active arrangements within reporting month

[Adnan_Razaq]

Hi,

I have two base datasets

data base_1;
input ID$ Month date9.;

format month date9.;
datalines;

1 31JAN2017 

1 28FEB2017 

1 31MAR2017

1 30APR2017 

1 31MAY2017

2 31Jan2017

2 28FEB2017 

2 31MAR2017 

2 30APR2017 

2 31MAY2017

3 31JAN2017

3 28FEB2017

3 31MAR2017

3 30APR2017

3 31MAY2017

;

data base_2;
input ID$ sols_instructed_date sols_withdrawn_date ;
datalines;

1 15JAN2017 28MAR2017

2 04FEB2017 25FEB2017

3 04APR2017 .

;

The dataset which I need is

ID	Month	sols_instructed_date	sols_withdrawn_date
1	31-Jan-17	15-Jan-17	.
1	28-Feb-17	15-Jan-17	.
1	31-Mar-17	15-Jan-17	28-Mar-17
1	30-Apr-17	.	.
1	31-May-17	.	.
2	31-Jan-17	.	.
2	28-Feb-17	04-Feb-17	25-Feb-17
2	31-Mar-17	.	.
2	30-Apr-17	.	.
2	31-May-17	.	.
3	31-Jan-17	.	.
3	28-Feb-17	.	.
3	31-Mar-17	.	.
3	30-Apr-17	04-Apr-17	.
3	31-May-17	04-Apr-17	.

[RW9]

And what is the basis for the merge?  Why would row 1 of base_2 appear on row 3 of the output dataset, I can see no logical reason.  Perhaps it is where withdrawn date is less than month - note why have you called the date variable month?

[Erkika]

This is a very curious output you want, but here's my crack at it.

Since you have missing values in the withdrawn date, create a copy of base_2 with today's date in the withdrawn date; merge that dataset to base_1 based to get the instructed date; then merge that new dataset with base_2 to get the withdrawn date.

data base_2a;
set base_2;
if sols_withdrawn_date eq . then sols_withdrawn_date = today();
run;

proc sql;
create table merge
as select 
base_1.*,
base_2a.sols_instructed_date
from base_1
left join base_2a
on base_1.ID=base_2a.ID
and month(base_1.month) >= month(base_2a.sols_instructed_date)
and month(base_1.month) <= month(base_2a.sols_withdrawn_date)
;

create table want
as select 
merge.*,
base_2.sols_withdrawn_date
from merge
left join base_2
on merge.ID = base_2.ID
and month(merge.month) = month(base_2.sols_withdrawn_date)
order by ID, month;
quit;