topic Re: Run teradata code from file and save as a SAS dataset in SAS Programming

Run teradata code from file and save as a SAS dataset

lakshayvohra — Wed, 22 May 2019 12:27:33 GMT

In my organization some of the teams are working on Teradata while some are working on SAS depending on client requirement. I work on SAS but sometimes we need to replicate processes which are setup in Teradata(by other teams) to SAS.

Initially I thought that I could simply take the Teradata code and paste it in a proc sql step and it would work. However, a lot of functions which work in Teradata are not compatible with proc sql. So usually I replicate those steps in SAS by rewriting the code. This takes a lot of time.

I was wondering if there is a method by which I can directly call a Teradata code (saved in an SQL file) from SAS such that it runs in Teradata and the results get exported as a SAS dataset. This would eliminate the need to replicate the SQL codes in SAS.

I already have a code setup through which I connect to the teradata server and then i simply use the tables present there in SAS. It looks something like this:

libname mylib teradata user=testuser password=testpass;

However, I am not able to figure out how I can directly call a SQL code file from SAS such that it runs in Teradata. What I am looking for would involve these steps:

Save the Teradata code in an SQL file
Call the file from SAS such that it runs in Teradata
Import the code output to a SAS dataset

It feels that the step three is not directly possible so thought maybe I could do it in two steps - export the Teradata outputs to excel and then import the excel to SAS using proc import. I looked on the internet and found some functions like bteq export etc. but I am not able to put everything together.

Re: Run teradata code from file and save as a SAS dataset

Tom — Wed, 22 May 2019 12:42:57 GMT

You can use pass through SQL code in SAS. So assuming you have already made the libref MYLIB the basic syntax is to either pull the results of a query. Or execute a statement.

proc sql;
connection using mylib ;
create table work.test as select * from connection to mylib
( .... teradata select statement goes here ....)
;
execute 
( .... teradata statement goes here ....)
by mylib;
quit;

Re: Run teradata code from file and save as a SAS dataset

LinusH — Wed, 22 May 2019 13:10:09 GMT

If you really want to call a seperate program, try %include within the SQL pass through block as shown by @Tom. (untested)

IN the long, I don't think it's good practice to separate those elements since you can't see in your SAS program what the Teradata SQL returns.