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 | . |
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?
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.