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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.