SAS connect Teradata and execute

[RatoVampiro]

Hi all.
 
I tryng execute on teradata a Insert procedure from SAS, but i receive a strange error .
I need execute:

proc sql;
connect to teradata as tera (user=xxxxx password=xxxxx database='ZZZ' tdpid='db1xxxxh.xxx.xxxxx.xxx' MODE=TERADATA );
execute (
    INSERT INTO D_REL_PDD_V.VW_RTDM_SOLICITACAO
            (OID_RTDM_SOLICITACAO, NOM_FLUXO, NOM_TIPO, NOM_SUBTIPO, COD_AGENCIA)
    SELECT
            OID_RTDM_SOLICITACAO,
            NOM_FLUXO,
            NOM_TIPO,
            NOM_SUBTIPO,
            COD_AGENCIA
    FROM
            WORK.TESTE_TERA)
by tera;
execute (commit) by tera;
disconnect from tera;
quit;

 
This is a error:
ERROR: Teradata execute: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword
       or '(' between the 'FROM' keyword and the 'WORK' keyword.
======================================================
 
Any ideas?
tks a lot!!!

[Tom]

The EXECUTE ()  BY connection_name  statement means what it sounds like. You can only place code that the remote database can execute.  In particular the Teradata server knows nothing about your SAS work library.

If you want to insert data into a Teradata table from a SAS dataset then I would recommend making a libref that points to the Teradata database you are writing to and then just write the INSERT statement in PROC SQL (without the EXECUTE() statement). Or better still just use normal SAS statements instead of using PROC SQL at all.

libname TERA teradata
  user=xxxxx password=xxxxx
  database='D_REL_PDD_V'
  tdpid='db1xxxxh.xxx.xxxxx.xxx'
  MODE=TERADATA 
;

proc append
    data=work.teste_tera
    base=tera.VW_RTDM_SOLICITACAO
;
run;

[RatoVampiro]

Hi Tom. Tks by your help.

By libref, i have success.

But in my insert i need concatenate two fields with legth of 32k and by libref this not work.

Cause this i need to run insert by EXECUTE().

[Tom]

Upload the data first and then use EXECUTE () BY to do the concatenation.

%let username=XXXX;

libname TDWORK teradata
  user=&USERNAME password=xxxxx
  database=&USERNAME
  tdpid='db1xxxxh.xxx.xxxxx.xxx'
  MODE=TERADATA 
  CONNECTION=GLOBAL
  DBMSTEMP=yes
;

proc sql noprint ;
    connect to teradata  as tera
( user=&USERNAME password=xxxxx
  database=&USERNAME
  tdpid='db1xxxxh.xxx.xxxxx.xxx'
  MODE=TERADATA 
  CONNECTION=GLOBAL
) ;
  create table TDWORK.TESTE_TERA as 
     select * from work.teste_tera
  ;
  execute (
    INSERT INTO D_REL_PDD_V.VW_RTDM_SOLICITACAO
            (OID_RTDM_SOLICITACAO, NOM_FLUXO, NOM_TIPO, NOM_SUBTIPO, COD_AGENCIA)
    SELECT
            OID_RTDM_SOLICITACAO,
            NOM_FLUXO,
            NOM_TIPO,
            NOM_SUBTIPO,
            COD_AGENCIA
    FROM
            TESTE_TERA
   ) by TERA;
   drop table tdwork.teste_tera ;
quit;

[RatoVampiro]

Hi Tom!

The solution work!!

Tks!!!!!