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.
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
`1 4-10-2009` and `1 4-11-2009`
they are there in want. I can not understand what do u mean.
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;
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;
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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.