Join/merge with dates

[overmar]

Ok so the issue was that you actually only wanted one row per line and you wanted the minimum of the values which met your requirements. Data step does a much better job of picking off the first observation, but if you wanted to use SQL and combine everything into one statement the below code will work. It takes the distinct IDs and their date from table one and looks at the difference between a.date and b.date and then outputs all of those which are between 1 and 31 days and it then takes the minimum date and only reports that as date2. Since distinct and group by were used and there are no duplicate values for a.date this code will produce 3 IDs, 3 dates, and the first date for all 3 IDs and Dates which met your criteria.

proc sql;  

create table want

as select distinct  a.id,a.date, (min(case when datdif(a.date,b.date,'act/act') between 1 and 31 then b.date else . end)) format=mmddyy10. as date2

from one as a left join two as b on a.id eq b.id

group by a.id;

quit;

[bollibompa]

Thanks!!