SAS: How do I write a pass-through to Teradata query without bringing back the results?

Question

I want write a passthrough query in SAS but I don't want to bring back the results and I want to store the results in Teradata.

In Teradata SQL I can do

create table db.table2 as (select
        column1,
        column2,
        count(*) as cnt
    from
        db.table1
    group by 
        column1, column2
    having cnt = 1) with data primary index(column1, column2)
)

In SAS there is only examples of pass-through query that returns some results into SAS. In this case I just want to use SAS to create a table on Teradata without leaving the SAS interface (I can do this in Teradata Studio Express) but it breaks the workflow.

How do I do a "pass-through" without returning the results? The below doesn't seem to work. What's the right for what I want to do?

PROC SQL;
    CONNECT TO TERADATA(user="user" password="pw" server ="Teradata.something.somehwere");


    drop table teradata.sometable   ;

    DISCONNECT FROM TERADATA;
QUIT;

Does it work if you include a dummy query that returns e.g. 1 row? — user667489, Dec 11 '14 at 08:42
Some things, like create/drop table in many cases, you can do from a LIBNAME connection - is that feasible here? — Joe, Dec 11 '14 at 16:15

score 4 · Accepted Answer · edited Dec 11 '14 at 23:14

You need the EXEC statement:

PROC SQL;
    CONNECT TO TERADATA(user="user" 
                        password="pw" 
                        server ="Teradata.something.somehwere");

    EXEC( ) BY TERADATA;
    EXEC( Commit ) BY TERADATA; /* this is needed if the above statement modifies the database in anyway) */

    drop table teradataserver.sometable ;
    DISCONNECT FROM TERADATA;
QUIT;

Within the brackets you can insert a Teradata SQL statement that will run on the DB and keep the results on the DB. So, in full:

PROC SQL;
    CONNECT TO TERADATA(user="user" 
                        password="pw" 
                        server ="Teradata.something.somehwere");

    EXEC(
      create table db.table2 as 
        (
        select
        column1,
        column2,
        count(*) as cnt
        from
        db.table1
      group by 
        column1, column2
      having cnt = 1
        ) with data primary index(column1, column)
    ) BY TERADATA;

    DISCONNECT FROM TERADATA;
QUIT;

For info, the BY part specifies which DB to process the statement on, if we were connecting to more than one DB within the SQL Procedure, e.g.

PROC SQL;
  CONNECT TO TERADATA AS T1 (...);
  CONNECT TO TERADATA AS T2 (...);
  EXEC(...) by T1;
  EXEC(...) by T2;
  DISCONNECT FROM T1;
  DISCONNECT FROM T2;
QUIT;

I got this error when I tried to run the code and I got this error ERROR: Teradata execute: Only a COMMIT WORK or null statement is legal after a DDL Statement.What does it mean? — xiaodai, Dec 11 '14 at 22:19
You may need to add that statement immediately after your first EXEC, e.g. `EXEC(COMMIT WORK) by TERADATA;`, this is because you've asked to make changes to the database by submitting the `CREATE TABLE` and then tried to disconnect without committing the changes. — mjsqu, Dec 11 '14 at 22:49

SAS: How do I write a pass-through to Teradata query without bringing back the results?

1 Answers1