topic PROC SQL in SAS Programming

PROC SQL

irinaia — Thu, 01 Nov 2018 06:42:43 GMT

Hi

I just want to clarify in what order given steps will be executed in proc sql.

Let us say we have table A 91 000 000 rows and table B with 2 900 000.

We want to left join these table on id and using where statement filter out based on a condition.

proc sql;
create table test as
select a.*
from a
left join b on a.id=b.id
where a.status=1;

Will tables be joined first and then filter in where close will be applied?

Or are we reading row by row from table a, checking condition and if it met run left join?

What is the best way to join such a big tables?

Re: PROC SQL

RW9 — Thu, 01 Nov 2018 08:54:09 GMT

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;

Re: PROC SQL

singhsahab — Thu, 01 Nov 2018 09:04:03 GMT

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;

Re: PROC SQL

irinaia — Thu, 01 Nov 2018 11:07:14 GMT

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.

Re: PROC SQL

RW9 — Thu, 01 Nov 2018 11:18:14 GMT

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.

Re: PROC SQL

irinaia — Thu, 01 Nov 2018 12:19:42 GMT

Do you mean that the whole sql querie against RDBMS will be always executed on SQL side and SAS will only get returned the final result or it will return row by row? I just want to understand where query will be executed, in what order and so on.

Re: PROC SQL

kiranv_ — Thu, 01 Nov 2018 13:22:15 GMT

if the data in rdbms, which ebdms is it?

Re: PROC SQL

irinaia — Thu, 01 Nov 2018 13:24:10 GMT

MS SQL Server

Re: PROC SQL

RW9 — Thu, 01 Nov 2018 13:25:29 GMT

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:

http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#p1a9wh7qwv6vc9n18arz94i379q0.htm

Then the SQL is generated internally and sent to the DB for results to be passed back.

Re: PROC SQL

kiranv_ — Thu, 01 Nov 2018 13:29:39 GMT

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.