Disclaimer: Thanks to @Ksharp for providing extensive help on this issue, the speed of the code increased dramatically (saved several hours). @mkeintz has also provided an alternative solution using PROC SQL. If one wants to use PROC SQL, please see @mkeintz's solution (with the having clause) or @Ksharp's solution. Given that @Ksharp answered first, I had to accept his first. Both thanks so much to both of you! Question starts here: I am trying to find an alternative way to write the following code by not relying on in: proc sql;
create table work.sample1 as
select a.*, b.*
from WORK.IMPORT2 as a
right join work.portfolio as b
on a.mgrno=b.mgrno
where a.fdate in(
select max(fdate) as last_fdate
from WORK.IMPORT2
where a.mgrno=b.mgrno and fdate < Announcement);
quit; The dataset "work.portfolio" contains entries as follows: mgrno announcement 9385 9/16/09 62890 9/14/08 The dataset "WORK.IMPORT2" contains entries as follows: mgrno fdate cusip 9385 12/31/07 12345 9385 12/31/08 12345 9385 12/31/09 12345 9385 12/31/07 23456 9385 12/31/08 23456 9385 12/31/07 34567 9385 12/31/07 78911 9385 12/31/08 78912 62890 9/13/08 34567 62890 9/13/07 12345 62890 9/13/11 22345 I have the following two questions: Q1: Is there an alternative (and more efficient) way of writing the code stated above without using IN( )? In particular, I am trying to select the most recent fdates that are before announcement. Q2: How can I select the most recent fdates by cusip and mgrno such that fdate < announcement? In Q1, I am only concerned about the most recent last fdates by MGRNO, but here I also want to see how to break down this specification into MGRNO and CUSIP. Thanks in advance for any help / suggestions.
... View more
