BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Jacek2
Obsidian | Level 7

Hi
I have table wsad with 1,6 mln obs and table limit with 0,4 mln obs.
the code below works with several hundred records, but freezes on more
What to do to make it work with more records?

proc sql; 
update WSAD as a 
set pkt = (select pkt from limit b where a.id= b.id),
     data = (select data from limit b where a.id = b.id)
 where a.id in (select id from limit);
 quit;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

From your code, I guess that id is unique in both datasets.

This should be much faster:

proc sort data=wsad;
by id;
run;

proc sort data=limit;
by id;
run;

data want;
update
  wsad
  limit (keep=id pkt data)
;
by id;
run;

PS sub-selects are notoriously slow in SAS SQL.

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

From your code, I guess that id is unique in both datasets.

This should be much faster:

proc sort data=wsad;
by id;
run;

proc sort data=limit;
by id;
run;

data want;
update
  wsad
  limit (keep=id pkt data)
;
by id;
run;

PS sub-selects are notoriously slow in SAS SQL.

Jacek2
Obsidian | Level 7
Thank you Kurt, it works great and very fast !!!
FreelanceReinh
Jade | Level 19

Hi @Jacek2,

 

If there is a requirement to edit dataset WSAD in place (which is what your original code does) and you want to avoid sorting, you can use the MODIFY statement of the DATA step together with a hash object:

data wsad;
if _n_=1 then do;
  dcl hash h(dataset:'limit');
  h.definekey('id');
  h.definedata('pkt','data');
  h.definedone();
end;
modify wsad;
if h.find()=0;
run;
Jacek2
Obsidian | Level 7
Thanks for the answer, it's always nice to get to know something new.
Jacek2
Obsidian | Level 7
What if there is no need to edit?
FreelanceReinh
Jade | Level 19

If you don't mind overwriting WSAD with the updated version, but you still want to avoid sorting, you can replace these two lines

modify wsad;
if h.find()=0;

by these three

set wsad;
rc=h.find();
drop rc;

in my code.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 857 views
  • 2 likes
  • 3 in conversation