BookmarkSubscribeRSS Feed
user24
Obsidian | Level 7

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

1 REPLY 1
Shmuel
Garnet | Level 18

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;

 

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1440 views
  • 0 likes
  • 2 in conversation