I frequently get requests for records we store in a large sas file. The table has approx 3 million records and 500 fields (about 10gb). The records I need I import into in a small table of approx 2000-3000 records, with only one field. Using PROC SQL is unacceptably slow, this would take probably a fraction of a second on a sql server. Instead SAS takes 12-15 minutes. How can I speed this up?
proc sql;
create table results as
select a.*
from
large_table a
left join
small_table b
on a.record_id = b.record_id
where b.record_id ne '';
quit;
If I have a smaller number of records, generally I'll just put the records in a where condition and load the file into memory (I have 16gb ram) via SASFILE, if there are only a couple hundred records, I get results in maybe 1-2 seconds.
sasfile.large_table load;
proc sql;
create table results as
select *
from large_table
where record_id in ('0000000001','0000000002');
quit;
This method works great!! But there's a limit to how many records I can put in the where statement. I'd like to find a method that works as fast, using a selection criteria based on the contents of a small sas table.
Thanks
1. If your second solution worked well for you, then why not stick to it and using a subquery to accommodate your small table:
sasfile.large_table load;
proc sql;
create table results as
select *
from large_table
where record_id in (select record_id from small_table);
quit;
2. You could try Hash table by putting the small table in the Hash:
data want;
if _n_=1 then do;
declare hash h(dataset:'small_table');
h.definekey('record_id');
h.definedone();
end;
set large_table;
if h.check()=0;
run;
3. If both of your tables are presorted by 'record_id', merge can also be an option:
data want;
merge small(in=s) big(in=b);
by record_id;
if a and b;
run;
BTW, I am puzzled by your first solution, isn't it to be 'right join' in this case?
Haikuo
If the second code works quickly I am guessing that the large table is sorted/indexed by ID, for the first piece of code make sure the small table is also sorted/indexed by ID. You should put the small table first in your code, the first table you reference will be put into your memory. Use the smaller indexed table first and it might improve performance.
Why use a left join, an inner join would do it without having to put a where statement.
Indexing by ID and putting the small table first reduces time from 20 minutes (I looked back at the log for actual results, I waaay underestimated earlier. Really 20 minutes!!!) to 12 minutes. I'm testing some of the other ideas.
1. If your second solution worked well for you, then why not stick to it and using a subquery to accommodate your small table:
sasfile.large_table load;
proc sql;
create table results as
select *
from large_table
where record_id in (select record_id from small_table);
quit;
2. You could try Hash table by putting the small table in the Hash:
data want;
if _n_=1 then do;
declare hash h(dataset:'small_table');
h.definekey('record_id');
h.definedone();
end;
set large_table;
if h.check()=0;
run;
3. If both of your tables are presorted by 'record_id', merge can also be an option:
data want;
merge small(in=s) big(in=b);
by record_id;
if a and b;
run;
BTW, I am puzzled by your first solution, isn't it to be 'right join' in this case?
Haikuo
Another approach if the second method worked for you ...
A single macro variable is plenty large enough to hold 3000 quoted IDs. Why not automate that process:
proc sql noprint;
select "'" || strip(record_id) || "'" into : macro_var separated by ',' from smaller_table;
quit;
Then use the macro variable:
proc sql;
create table results as
select *
from large_table
where record_id in (¯o_var);
quit;
It still has to be tested for timing, since searching through a list of 3,000 will take longer than searching through a list of 3. But any solution based on a single table is likely to run faster.
Good luck.
OK, we have a winner Hai.kuo, answer #1. I should have thought to use a subquery, I guess I didn't know how it would execute with sasfile. The following (once sasfile loads) executes in 4.1 seconds:
sasfile.large_table load;
proc sql;
create table results as
select *
from large_table
where record_id in (select record_id from small_table);
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 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: