- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug
"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings
SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
`1 4-10-2009` and `1 4-11-2009`
they are there in want. I can not understand what do u mean.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug
"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings
SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;