how to make select in select max(date) sql code seperate two code

[user24]

I have a query like select in select as below

But sas is so slow when i run that. Can i  make it seperate for make it faster?

PROC SQL;
CREATE TABLE WORK.FLA2 AS
SELECT DISTINCT

B2.perfjob_pidm,
B3.capture_date,
B3.locked_date
FROM work.PERFJOB AS B2,
work.Z_HR AS B3
WHERE 
B2.perfjob_pidm = B3.perfjob_pidm
AND B2.perfjob_effective_date = B3.perfjob_effective_date
AND B3.locked_date =
(
select max(y.locked_date)
from work.Z_HR y
where 
y.perfjob_pidm = B3.perfjob_pidm
and y.perfjob_effective_date = B3.perfjob_effective_date
);

[Shmuel]

Is the history file mutch bigger than the PERFJOB file ?

Is the history dataset sorted by the group variables or at least have them as indexes ?

Then try next code, which I couldn't run and check.

proc sql;

       create table temp as select

       perfjob_term_code, perfjob_pidm, perfjob_posn, perfjob_effective_date,

       capture_date,

       max(y.locked_date) as locked_date
from work.Z_HR_FLAC_PERFJOB_history y
group by  perfjob_term_code, perfjob_pidm, perfjob_posn, perfjob_effective_date;

      create table WORK.FLA2 AS

      SELECT DISTINCT
          B2.perfjob_term_code,
          B2.perfjob_pidm,
          B2.perfjob_posn ,
          B3.capture_date,
          B3.locked_date
     FROM work.PERFJOB AS B2,

     left join temp as B3
    on     B2.perfjob_term_code = B3.perfjob_term_code
     AND B2.perfjob_pidm = B3.perfjob_pidm
     AND B2.perfjob_posn = B3.perfjob_posn
     AND B2.perfjob_effective_date = B3.perfjob_effective_date;

quit;