CODEWITHSUNDEEP
sasteradata
xiaodai
xiaodai

Reputation: 16054

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

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;

Upvotes: 1

Views: 6613

Answers (1)

mjsqu
mjsqu

Reputation: 5452

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;

Upvotes: 4

Related Questions