topic Re: looking for suggestion about proc sql connect to DB optimized query in SAS Programming

looking for suggestion about proc sql connect to DB optimized query

Aexor — Wed, 26 Apr 2023 18:58:09 GMT

Hi ,

I have a query. Currently I am facing ERROR: Insufficient space in file and time issue. I am working on optimization of a SAS code. FOr the same matter I need one suggestion

In the existing code I can see multiple external db connection to create some tables in database as

for e.g

step 1 . proc sql noprint;

connect to db (<credential>);

execute (

create table abc

) by db.;

disconnect from db;

;

quit;

step 2 .

proc sql noprint;

connect to db (<credential>);

execute (

create table xyz

) by db.;

disconnect from db;

;

quit;

like that I have multiple steps, I can make only one connection and create both xyz and abc table.

will this help in any sort of optimization. any explanation will be very helpful. Thanks!!!!!!

Re: looking for suggestion about proc sql connect to DB optimized query

LinusH — Wed, 26 Apr 2023 19:38:46 GMT

A full log would be helpful.

And I'm not sure what you mean by optimizing the queries (especially when you don't show them).

The no of connect/execute shoudldn't matter.

Also, if all your queries are within EXECUTE blocks, the performance issue is most likely in the external DB, not in SAS. So perhaps try to get help from your DBA or any other knowledable in your database.

Re: looking for suggestion about proc sql connect to DB optimized query

SASKiwi — Wed, 26 Apr 2023 20:10:18 GMT

Please post a complete log of your program including notes and errors. Posting errors like "ERROR: Insufficient space in file and time issue" without seeing the code that caused them means we can't provide useful advice.