Help using Base SAS procedures

proc sql query 1 vs query 2

Reply
Regular Contributor
Posts: 163

proc sql query 1 vs query 2

HI:
I am wandering why would the first take a lot longer then the second query in proc sql , the difference is noted in NOTE DIFFERENCE comment.


:
/*Query 1 */
proc sql;

select a.id,
b.name

from transactions as a ,
person as b
where
a.id = b.id
and
b.seq = (
select max(sub.seq)
from person as sub
where b.id = sub.id /*NOTE DIFFERENCE*/
)
quit;


/*Query 2 */
proc sql;

select a.id,
b.name

from transactions as a ,
person as b
where
a.id = b.id
and
b.seq = (
select max(sub.seq)
from person as sub
where a.id = sub.id /*NOTE DIFFERENCE*/

)
quit;
Trusted Advisor
Posts: 2,113

Re: proc sql query 1 vs query 2

There are a lot of POSSIBLE reasons that are data dependent. Without knowing the data, we are shooting in the dark. Possible reasons include:
-- size of data set a vs b.
-- indexing on a vs b.
-- location of a vs b (e.g. SAS dataset or Oracle database).
-- data set type (view vs dataset)

Doc Muhlbaier
Duke
Super User
Posts: 10,516

Re: proc sql query 1 vs query 2

As a minor addition to Docs comment on location: network drive vs local if your network runs like mine.
Ask a Question
Discussion stats
  • 2 replies
  • 130 views
  • 0 likes
  • 3 in conversation