You'll need something like this:
[pre]
data OUT;
set SASHELP.CITIDAY(keep=DATE SNYDJCM);
DSID=open('SASHELP.CITIDAY(keep=DATE SNYSECM where=(DATE > "10jan1988"d and (DATE between ' ||
put(DATE-1,5.)||' and '||put(DATE+1,5.)||')))');
RC=fetch(DSID);
if RC=0 then
do while(RC=0);
SNYSECM=getvarn(DSID,varnum(DSID,'SNYSECM')); *replace varnum() by the number to speed up a wee bit;
output;
RC=fetch(DSID);
end;
else output;
RC=close(DSID);
run;
[/pre]
(I left join the table to itself within 1 day here).
Also, you'll need an index on the secondary table of course.
If you have version 9.2, you might want to look at hash tables as this version allows hash tables with non-unique keys.
2 dates + cusid + w/off + interest occupy 4+4+8+8+8=32 bytes. That's 33 million rows per GB of RAM.
Otherwise, the logic above with the secondary table loaded in memory using a
sasfile statement is another option.
Message was edited by: Chris@NewZealand