topic Re: SAS connect Teradata and execute in SAS Procedures

SAS connect Teradata and execute

RatoVampiro — Tue, 20 Oct 2015 16:43:04 GMT

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!!!

Re: SAS connect Teradata and execute

Tom — Tue, 20 Oct 2015 17:12:21 GMT

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;

Re: SAS connect Teradata and execute

RatoVampiro — Tue, 20 Oct 2015 17:20:58 GMT

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().

Re: SAS connect Teradata and execute

Tom — Tue, 20 Oct 2015 17:35:07 GMT

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;

Re: SAS connect Teradata and execute

RatoVampiro — Wed, 21 Oct 2015 13:23:03 GMT

Hi Tom!

The solution work!!

Tks!!!!!