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;
Thanks!!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.