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.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.