PROC SQL HOW TO SPLIT THE CODE INTO TWO PARTS ??

Reply
Contributor
Posts: 27

PROC SQL HOW TO SPLIT THE CODE INTO TWO PARTS ??

Suppose I have a complex code as follows:-

I am not typing the whole code here, however, hopefully it makes sense .. :smileyplain:

Proc sql;

connect to - xxx;

create table ABC as

select * from .......

where PId in

(select distinct pid from patient_Table

where Pat_Code=5);

disconnect;

quit;

Now i have an issue running the whole code and it is taking forever since i am using where clause. Pls suggest how to split this into two parts..

P.S. the from table i.e. from where we are pulling the data is from a ms sql table..

Super User
Posts: 3,238

Re: PROC SQL HOW TO SPLIT THE CODE INTO TWO PARTS ??

Your sub-query is not fully qualified so for every row in your outer query you are reading multiple rows from your patient table so it is not surprising it is taking a long time to run.

Try something like this:

create table ABC as

select * from ....... as a

join

(select pid

from patient_Table 

where Pat_Code=5

) as b

on a.pid = b.pid;

Super User
Posts: 9,878

Re: PROC SQL HOW TO SPLIT THE CODE INTO TWO PARTS ??

If I remembered right IN only support a couple of thousand value . or try operator EXIST .

create table ABC as

select * from have as a

where

exists(select *  from patient_Table as b

where b.Pat_Code=5 and b.pid=a.pid );

Another example :

proc sql;

  select * 

  from proclib.payroll p 

  where exists (select * 

  from proclib.staff s

  where p.idnumber=s.idnum 

  and state='CT');

Xia Keshan

Super User
Super User
Posts: 7,727

Re: PROC SQL HOW TO SPLIT THE CODE INTO TWO PARTS ??

I see you are connecting to a database, maybe push the processing out to that:

proc sql;

     connect to MySQL (...);

     execute by MySQL (create table myresults as select * from have where ...);

     create table MY_RESULTS as

     select     *

     from        connection to MYSQL (

     select * from myresults);

quit;

Ask a Question
Discussion stats
  • 3 replies
  • 4577 views
  • 0 likes
  • 4 in conversation