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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.