BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Maaz
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
hollandnumerics
Pyrite | Level 9

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:

  1. extracting 8 (potentially vast?) tables into SAS
  2. merging 4 tables each into 2 other temporary SAS tables
  3. joining the 2 temporary SAS tables with UNION into another temporary SAS table
  4. 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

View solution in original post

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

What does your code look like?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

hollandnumerics
Pyrite | Level 9
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!
Philip R Holland
Holland Numerics: Blog and Forums
http://blog.hollandnumerics.org.uk
PGStats
Opal | Level 21

Try the advice given in

 

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n1wue1kaft1rlsn1v0...

 

before considering converting to a SAS program.

PG
Maaz
Calcite | Level 5

th

Capture.PNG

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 

hollandnumerics
Pyrite | Level 9

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:

  1. extracting 8 (potentially vast?) tables into SAS
  2. merging 4 tables each into 2 other temporary SAS tables
  3. joining the 2 temporary SAS tables with UNION into another temporary SAS table
  4. 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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 883 views
  • 0 likes
  • 5 in conversation