## Active arrangements within reporting month

Occasional Contributor
Posts: 15

# Active arrangements within reporting month

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 .

Super User
Posts: 7,970

## Re: Active arrangements within reporting month

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?

New Contributor
Posts: 2

## Re: Active arrangements within reporting month

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;``````
Discussion stats
• 2 replies
• 73 views
• 0 likes
• 3 in conversation