BookmarkSubscribeRSS Feed
Adnan_Razaq
Calcite | Level 5

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.

 

 

 

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 581 views
  • 0 likes
  • 3 in conversation