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

user24 — Mon, 12 Sep 2016 19:50:56 GMT

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
);

Re: seperate the sql code

Shmuel — Mon, 12 Sep 2016 19:50:53 GMT

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;

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

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

Re: seperate the sql code