topic Re: left join with condition in SAS Programming

left join with condition

ifti_ch2002 — Tue, 19 May 2020 16:07:47 GMT

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.

Re: left join with condition

yabwon — Tue, 19 May 2020 16:25:13 GMT

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

Re: left join with condition

ifti_ch2002 — Tue, 19 May 2020 16:27:56 GMT

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

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

Re: left join with condition

novinosrin — Tue, 19 May 2020 16:34:41 GMT

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;

Re: left join with condition

yabwon — Tue, 19 May 2020 16:47:46 GMT

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;

Re: left join with condition

s_lassen — Wed, 20 May 2020 10:22:05 GMT

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;