DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 33

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

[ Edited ]

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

Trusted Advisor
Posts: 1,399

Re: seperate the sql code

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;

 

 

Ask a Question
Discussion stats
  • 1 reply
  • 282 views
  • 0 likes
  • 2 in conversation