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

Hello all,

I want to know whether can we use teradata or sql or Oracle functions in the expression field of table loader or teradata table loader in sas DI or not.

I was using teradata table loader and one of my column I was using teradata function (current_timestamp) inside expression of table loader.

It's throwing error as current_timestamp function is not found. Same case for if function also.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

What can be done depends on your version of DI Studio. Besides of consulting the documentation what I'm often doing is having a look into the generated code. That normally tells you very fast what's working/not working and why.

From the documentation

SAS(R) Data Integration Studio 4.9: User's Guide

http://support.sas.com/resources/papers/proceedings10/116-2010.pdf

What you probably want to do is ELT instead of ETL - so you would load your source into a Teradata staging table first. Or you could load via a Teradata view which has the logic/transformation built in.

View solution in original post

8 REPLIES 8
Patrick
Opal | Level 21

What can be done depends on your version of DI Studio. Besides of consulting the documentation what I'm often doing is having a look into the generated code. That normally tells you very fast what's working/not working and why.

From the documentation

SAS(R) Data Integration Studio 4.9: User's Guide

http://support.sas.com/resources/papers/proceedings10/116-2010.pdf

What you probably want to do is ELT instead of ETL - so you would load your source into a Teradata staging table first. Or you could load via a Teradata view which has the logic/transformation built in.

Hercules
Fluorite | Level 6

Thank you Patrick for the kind reply.

Can you please specify why is it necessary to first load the data into a staging area (teradata table)  to use teradata functions in the expression of table loader?

Thanks

Patrick
Opal | Level 21

When you load from a SAS source table into a target Teradata table and you're using functions in the expression field: Where would you expect that these function are getting used (on the SAS or Teradata side)?

I can only repeat: Look into the generated code and things will become obvious.

Hercules
Fluorite | Level 6

Ok. So it means to use any of the sql or Oracle or teradata functions, it needs to be first staged into its respective table and then we can use its respective functions. That makes sense.

I had look into the code as well. Whenever I was using any of the expression inside table loader, it gets into the dataset I. E.

Data null;

Set W9fS0I;

Where current_timestamp ;

Run;

Something like that. And I was thinking, how can a teradata function will be used in sas dataset. And if that so, why sas has provided it.

Well thank you very much Patrick for the answer. I appreciate that.

Patrick
Opal | Level 21

Some of the DI transformations allow you to define functions as part of explicit pass-through SQl. In such cases the SQL gets executed directly in the data base and so the functions you can use there are the data base functions.

It depends on the DIS version if you can explicitly define where stuff gets executed. It also depends on the contexts where you're using the DIS transformations (e.g. the SQL join). If it's about joining two data base tables then I believe it would generate directly data base specific code - so you could use data base functions.

With the SQL transformation: If it uses "implicit" SQL and formulating stuff in SAS syntax the SAS/Access engine for the database will still try and push processing to the data base. It then depends on the actual SAS functions used if this is possible or not. Use "OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;" in your code as this will tell you what syntax SAS actually could send to the data base for processing.


Also consult the documentation for the SAS version and SAS/Access engine used to find out which SAS functions can be translated into data base functions so processing can be pushed to the data base. SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

LinusH
Tourmaline | Level 20

You could trigger ELT behaviour. From DI Studio Users Guide:

" When both the inputs and outputs of the Extract, SQL Join, Teradata Table Loader, and Table Loader transformations are stored in the same relational database, the code for these transformations can be pushed down to a database server for execution. This option increases performance by shifting data transformation to the most appropriate processing resource.

Note: The use of the Table Loader transformation in a pushdown job requires the following settings:

  • Load style: select either Append to Existing or Replace

  • New Rows: select Insert (SQL)"

Pushing ELT Job Code Down to a Database :: SAS(R) Data Integration Studio 4.9: User's Guide

http://support.sas.com/resources/papers/proceedings10/116-2010.pdf

Data never sleeps
Hercules
Fluorite | Level 6

Thank you so much LinusH. I'll try this and share with you what happened.

But I've a string feeling that this will work.

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
  • 8 replies
  • 1348 views
  • 1 like
  • 3 in conversation