Please use the code window, its the {i} for posting code. Your code doesn't make sense, you never use table b, so the statement is:
proc sql; create table test as select a.* from a a where status=1; quit;
That being said, its quite a lot of observations, so you may get better performance using a datastep (assumes they are sorted):
data test; merge a (where=(status=1) in=a) b (in=b); by id; if a; run;
Oh, and that where a.status will be executed after merge, you can subquery, eg.:
proc sql; create table test as select a.* from (select * from a where status=1) a
left join b b
on a.id=b.id; quit;
This is not a real code and the aim of it was just to show in what order statements are written.
What i want to know is in what order queries like this will be executed.
proc sql;
create table test as
select a.*, b.status
from a
left join b b
on a.id=b.id;
where current=1 and sum>0
quit;
Will SAS read row by row from database, check if it satisfy the condition in where close and then go through another table ,b-table, check if the row with same id exist there and then join.
So it is being executed on a database now? I am afraid I cannot hypothetical questions. If the SQL is being run within SAS then you can see the process using
proc sql _tree _method;
Options, that may help you. If its run on a database then you would need to check the SQL on the database itself.
Again, that depends on several different factors. If you use explicit pass-through, then all the SQL in there gets passed to the database for execution and results returned.
If your using a libname setup like:
Then the SQL is generated internally and sent to the DB for results to be passed back.
if the data in rdbms, which ebdms is it?
MS SQL Server
you can run showplan and you should try to run in sql server side itself. check whether your datasets have indexes. if you have access to sql server maangement studio and then you can see execution plan. it gives a very detailed info of how much time your query takes and which step it is taking time.
Hi ,
If there is no memory concern , kindly use hash object to join ...
here is a sample code.
data A;
input id status;
cards;
1 1
2 1
3 .
4 1
5 1
6 1
;
run;
data b;
input id;
cards;
1
2
3
5
;
run;
DATA WANT;
IF _N_=1 THEN DO;
DECLARE HASh H(DATASET:'B');
H.DEFINEKEY('ID');
H.DEFINEDATA('ID');
H.DEFINEDONE();
END;
DO UNTIL(LR);
SET A END=LR;
WHERE STATUS=1;
ARRAY ARR ID;
DO OVER ARR;
r=H.find(key:arr);
if r ge 0 THEN OUTPUT;
END;
END;
drop r;
RUN;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.