left join with condition

[ifti_ch2002]

Hi,

I have following data

data have1;
infile datalines missover; 
   input IDnumber @3 date  mmddyy10.;
   format date mmddyy10.;
   datalines;  
1 4-7-2003 
1 4-7-2009 
1 4-7-2010 
2 4-7-2005 
2 4-7-2011
;       
run; 

and 

data have2;
infile datalines missover; 
   input IDnumber @3 date  mmddyy10.;
   format date mmddyy10.;
   datalines;  
1 4-8-2003 
1 4-9-2003 
1 4-10-2009 
1 4-11-2009 
2 4-8-2005 
2 4-9-2005 
2 4-8-2011 
2 4-9-2011 
;       
run; 

and i need 

data want;
infile datalines missover; 
   input IDnumber @3 date  mmddyy10.;
   format date mmddyy10.;
   datalines;  
1 4-7-2003 
1 4-8-2003 
1 4-9-2003 
1 4-10-2009 
1 4-11-2009 
2 4-7-2005 
2 4-8-2005 
2 4-9-2005 
2 4-8-2011 
2 4-9-2011 
;       
run; 

What i need is to join have1 with have2 by id , check the dates in have 2, if greater than in have 1 then add the row under the first row in have 1.

[yabwon]

The Want doesn't seems to be what you described, it looks rather like: for a given ID if minimum value of date in have1 is less then dates in have2 then select minimum date from have 1 and all dates from have2 less than that value. 

From description it seem like for `1 4-7-2009` in have1 there should be two rows from have2 i.e. `1 4-10-2009` and `1 4-11-2009` but I don't see them in Want.

Could you clarify?

Bart

[ifti_ch2002]

`1 4-10-2009` and `1 4-11-2009` 

they are there in want. I can not understand what do u mean.

[novinosrin]

data have1;
infile datalines missover; 
   input IDnumber @3 date  mmddyy10.;
   format date mmddyy10.;
   datalines;  
1 4-7-2003 
1 4-7-2009 
1 4-7-2010 
2 4-7-2005 
2 4-7-2011
;       
run; 

data have2;
infile datalines missover; 
   input IDnumber @3 date  mmddyy10.;
   format date mmddyy10.;
   datalines;  
1 4-8-2003 
1 4-9-2003 
1 4-10-2009 
1 4-11-2009 
2 4-8-2005 
2 4-9-2005 
2 4-8-2011 
2 4-9-2011 
;       
run; 


proc sql;
create table want as
select *
from have1
group by idnumber
having min(date)=date
union all
select b.*
from 
(select * from have1 group by idnumber having min(date)=date) a
left join have2 b
on a.idnumber=b.idnumber
and a.date<b.date
order by idnumber,date;
quit;

[yabwon]

Yes they are, but the `1 4-7-2009` is not in the data, and from your description: "check the dates in have 2, if greater than in have 1 then add the row under the first row in have 1" I would expect to have also `1 4-7-2009` in the Want like:

data want;
infile datalines missover; 
   input IDnumber @3 date  mmddyy10.;
   format date mmddyy10.;
   datalines;  
1 4-7-2003 
1 4-8-2003 
1 4-9-2003 
1 4-10-2009 
1 4-11-2009 
1 4-7-2009 /**/
1 4-10-2009 /**/
1 4-11-2009 /**/
...
;       
run; 

[s_lassen]

This is, SQL-wise, not really a join, but a union. Which makes for rather complicated SQL, as you have to repeat the subquery for data from HAVE1.

I think it is a lot easier with a data step, e.g.:

data want;
  found=0;
  do until(last.IDnumber);
    set have1(in=in1) have2(in=in2);
    by IDnumber date;
    if in1 and found=0 then do;
      found=1;
      output;
      end;
    else if in2 and found=1 then
      output;
    end;
  drop found;
run;