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
);
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.