Run teradata code from file and save as a SAS dataset

[lakshayvohra]

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:

1. Save the Teradata code in an SQL file
2. Call the file from SAS such that it runs in Teradata
3. 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.

[Tom]

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;

[LinusH]

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.