Sorry - ignore that solution. It's rubbish. As I was walking @schnauzerrosie I rewrote it in my head. By turning table1 into a kind-of slowing-changing dimension, you can use SQL to create the output you want, with far fewer data contingencies. The only problem here would be if there were more than one error record within a date range.
This also, if your tables are really large, allows you to put integrity constraints on the tables, to make sure you're coping with duplicates. Note that I've created a low date for app_date_previous for the first instance per app_num - integrity constraints hate null values.
data table1;
infile cards;
attrib app_num length=8;
attrib app_date length=4 informat=date7. format=date9.;
input app_num
app_date;
cards;
1 01jan16
1 08jan16
1 13jan16
;
run;
data table2;
infile cards;
attrib app_num length=8;
attrib error_date length=4 informat=date7. format=date9.;
attrib error_code length=$ 3;
input app_num
error_date
error_code;
cards;
1 09jan16 xxx
;
run;
data table1_scd;
set table1;
retain app_date_previous;
attrib app_date_previous length=4 format=date9.;
by app_num;
if first.app_num then
app_date_previous = '1jan1900'd;
output;
app_date_previous = app_date;
run;
proc sql;
create table want as
select table1.app_num,
table1.app_date,
table2.error_date,
table2.error_code
from table1_scd as table1
left join table2
on table1.app_num = table2.app_num
and table2.error_date between table1.app_date_previous and table1.app_date
order by table1.app_num,
table1.app_date;
quit;
... View more
