SAS Sql subquery performance problem

[1162]

"It seems that SAS has to scan the whole SAS file in order to pick the few relevant fields (running the same code with fewer variables in the table decreases time immensely)."

Have you tried creating an index on your SAS dataset? That might speed things up.

PROC DATASETS LIBRARY=libname NOLIST;
MODIFY dataset;
INDEX CREATE varname;
RUN;
QUIT;

[1162]

If I'm reading the two queries correctly, it looks to me like query A is looking for a single account numbers while query B is looking for all account numbers. In query A, is the macro variable &acctno a single number (I think it has to be)? It doesn't look like query B has any filters that would result in a single account number being returned from the subquery.

Depending on the number of accounts, this could be the difference between returning 2,000 rows and 20,000,000 rows.

If this isn't the issue, I've always found it useful to try the same query in another application. This can sometimes narrow down whether the difference is due to the query or due to the application. I've run into situations where queries run much quicker in another application than in SAS (and vice versa).

[deleted_user]

Yes the result of query A is a single client_key which I am then using to gather multiple rows in query B (multiple instances of ip_addr), usually a few hundred.
As I posted I am used to running this in SQLServer and it runs in seconds.
Running queryB with the actual key value in the WHERE clause rather than the subquery returns in seconds. It would seem therefore that the problem is the use of the subquery.
What I am trying to find out is why, and if there is a solution. Being forced to use joins rather than subqueries, esp for such simple queries seems like a very crippled sql implimentation (if that is the cause).
I am new to SAS and Proc Sql and find that the performance of subqueries seems poor in general.