turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- sas Basic question for optimization

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2017 12:26 PM

Can converting a proc sql code in sas make some difference .My concern is time

as I have a long code in proc sql it takes more than half an hour to run.

if not how can I save my time?

Accepted Solutions

Solution

11-06-2017
03:17 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Maaz

11-03-2017 12:05 PM

Maaz,

As the whole program is SQL, why can't you recode it to run completely inside Teradata? Teradata is designed to run SQL efficiently, so why not let it get on with it.

Currently you are doing the following using PROC SQL:

- extracting 8 (potentially vast?) tables into SAS
- merging 4 tables each into 2 other temporary SAS tables
- joining the 2 temporary SAS tables with UNION into another temporary SAS table
- storing the final table back into Teradata

Doing the same steps all in Teradata using Pass-Thru will take SAS out the equation altogether.

Philip R Holland

Holland Numerics: Blog and Forums

http://blog.hollandnumerics.org.uk

Holland Numerics: Blog and Forums

http://blog.hollandnumerics.org.uk

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Maaz

11-02-2017 12:27 PM

What does your code look like?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Maaz

11-02-2017 12:51 PM

Without seeing any code its hard to say. Certainly in some circumstances SQL can be more resource intensive than a datastep or procedure variety. There are also other options however which may speed things up, for instance filtering data earlier so you only take in the rows you need, indexing data etc. Its quite hard to say though without any information.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Maaz

11-02-2017 01:24 PM

Maaz,

There is a simple correlation you should understand when posting a code-related question:

The probability of an answer is proportional to the amount of SAS code, log and output you post with your question!

There is a simple correlation you should understand when posting a code-related question:

The probability of an answer is proportional to the amount of SAS code, log and output you post with your question!

Philip R Holland

Holland Numerics: Blog and Forums

http://blog.hollandnumerics.org.uk

Holland Numerics: Blog and Forums

http://blog.hollandnumerics.org.uk

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Maaz

11-02-2017 02:20 PM

Try the advice given in

before considering converting to a SAS program.

PG

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Maaz

11-03-2017 05:23 AM

th

this is a small sample of code like this I have to run many more code which are more lenthy than this . the data is in Teradata and creating the table in tera data as well

Solution

11-06-2017
03:17 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Maaz

11-03-2017 12:05 PM

Maaz,

As the whole program is SQL, why can't you recode it to run completely inside Teradata? Teradata is designed to run SQL efficiently, so why not let it get on with it.

Currently you are doing the following using PROC SQL:

- extracting 8 (potentially vast?) tables into SAS
- merging 4 tables each into 2 other temporary SAS tables
- joining the 2 temporary SAS tables with UNION into another temporary SAS table
- storing the final table back into Teradata

Doing the same steps all in Teradata using Pass-Thru will take SAS out the equation altogether.

Philip R Holland

Holland Numerics: Blog and Forums

http://blog.hollandnumerics.org.uk

Holland Numerics: Blog and Forums

http://blog.hollandnumerics.org.uk