DATA Step, Macro, Functions and more

Active arrangements within reporting month

Reply
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

 

 

IDMonthsols_instructed_datesols_withdrawn_date
131-Jan-1715-Jan-17.
128-Feb-1715-Jan-17.
131-Mar-1715-Jan-1728-Mar-17
130-Apr-17..
131-May-17..
231-Jan-17..
228-Feb-1704-Feb-1725-Feb-17
231-Mar-17..
230-Apr-17..
231-May-17..
331-Jan-17..
328-Feb-17..
331-Mar-17..
330-Apr-1704-Apr-17.
331-May-1704-Apr-17.

 

 

 

Super User
Super User
Posts: 7,970

Re: Active arrangements within reporting month

Posted in reply to Adnan_Razaq

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

Posted in reply to Adnan_Razaq

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