SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
ifti_ch2002
Obsidian | Level 7

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.

5 REPLIES 5
yabwon
Onyx | Level 15

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



ifti_ch2002
Obsidian | Level 7

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

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

novinosrin
Tourmaline | Level 20
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
Onyx | Level 15

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



s_lassen
Meteorite | Level 14

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 885 views
  • 0 likes
  • 4 in conversation