topic Re: Regarding SAS Stored Procedures in SAS Procedures

Regarding SAS Stored Procedures

Nathan4 — Tue, 19 Dec 2017 15:37:13 GMT

I tried calling the stored procedure from Teradata to SAS. But I was getting an error as Invalid session mode. Can anyone help me with this error.

Below is the code and error:

proc sql;
connect to teradata as tera(user = testuser pass= testpass tpid="XXXXX" connection=global);
 Create table work.sasresult as
         Select * from connection to tera(call dbname.spname('2016-01-15','2016-01-30','IND',?,?));
disconnect from tera;
quit;

proc sql;
connect to teradata (user=&ishid password=&ishpass tdpid="XXXXXX" connection=global);
Execute(
create table work.spresult as
Select *
From Connection To teradata
(

Excute DB.SP('2017-01-01','2017-01-03','XXX',?,?) by teradata;
)
;
Disconnect From teradata;

quit;

ERROR: Teradata prepare: Invalid Session Mode for procedure execution. SQL statement was Call dbname.spname('2016-01-15','2016-01-30','IND',?,?);

Re: Regarding SAS Stored Procedures

ChrisHemedinger — Tue, 19 Dec 2017 15:40:57 GMT

Some hints on the Teradata community here:

https://community.teradata.com/t5/Database/Invalid-session-mode-for-procedure-execution/td-p/57172

You might need to adjust the session mode for how you connect to Teradata, which looks similar to encoding options for SAS (UTF-8 vs ANSI).

Re: Regarding SAS Stored Procedures

kiranv_ — Tue, 19 Dec 2017 16:03:01 GMT


connect to teradata as tera(user = testuser pass= testpass tpid="XXXXX" connection=global mode = teradata);

Try mode = Teradata along with other options in connect statement

Re: Regarding SAS Stored Procedures

Nathan4 — Tue, 19 Dec 2017 16:19:51 GMT

H Kiran,

I want call stored procedure from teradata to sas eg can you please give me some Exact syntax for that.

Thank you,

Nathan

Re: Regarding SAS Stored Procedures

Nathan4 — Tue, 19 Dec 2017 16:27:33 GMT

28 proc sql;
29
30 connect to teradata (user=&ishid password=&ishpass tdpid="xxxxxx" connection=global mode=Teradata );
31
32 Execute (call DB.SP('2017-01-01','2017-01-03',XXX',ORC,OMSG));
_
22
ERROR 22-322: Expecting a name.

Can you Please Help me out that

Re: Regarding SAS Stored Procedures

kiranv_ — Tue, 19 Dec 2017 16:40:17 GMT

I have never executed stored procedure in Explicit SQL pass through. but below is example for Oracle stored procedure executed through SAS(found the code on SAS site), I guess it should be similar expect that execute (execute in below code will become execute (call . I too did not find any solutions online and If this does not work, you should contact the expert at this kind of things @JBailey who could help you.

proc sql; 

connect to oracle (user=userid password=password path=database_name); 

execute (execute st_pr_name('parm')) by oracle; 

disconnect from oracle; quit;

Re: Regarding SAS Stored Procedures

ChrisHemedinger — Tue, 19 Dec 2017 16:47:16 GMT

There are a few other Teradata-specific examples located in this documentation. As @kiranv_ said, take heed of the proper syntax for the statements you're wanting to run.

  execute (
    call my_stp('param1', 'param2')
  ) by teradata;

Re: Regarding SAS Stored Procedures

Nathan4 — Tue, 19 Dec 2017 16:50:56 GMT

Hi,

28 proc sql;
29
30 connect to teradata (user=&ishid password=&ishpass tdpid="XXXX" connection=global mode=Teradata );
31
32 Execute (Execute DB.SP( '2016-01-01', '2016-01-03' , 'XXX' ))by Teradata;
ERROR: Teradata execute: 'DB.SP' is not a macro.

Re: Regarding SAS Stored Procedures

ChrisHemedinger — Tue, 19 Dec 2017 16:57:59 GMT

What happened to the "CALL" keyword? Why did you switch to EXECUTE within the Execute block?

I feel like you're thrashing here with your syntax attempts. Here's an example of a valid SQL step that calls a Teradata stored procedure.

proc sql;
   connect to teradata (user=userid password=xxxx server=myserver mode=Teradata);
   execute 
      (CALL SAS_SYSFNLIB.SAS_SCORE_EP
         (
            'MODELTABLE="grotto”.”sas_publish_model”',
            'MODELNAME=Intr_Tree',
            'INQUERY=SELECT * from ”grotto”.”score_input_table” WHERE x1 < 1.0',
            'OUTTABLE=”grotto”.”sas_score_out1”',
            'OUTKEY=id',
            'OPTIONS=’  /* can be blank or NULL if no options are needed */
         )
      ) by teradata;
   disconnect from teradata;
quit;

Re: Regarding SAS Stored Procedures

JBailey — Tue, 02 Jan 2018 19:41:12 GMT

Hi @Nathan4

I have taken a shot at explaining this via a SAS Communities article.

An Insider's Guide to using Teradata Stored Procedures with SAS/ACCESS

Let me know what you think.

Best wishes,

Jeff

Re: Regarding SAS Stored Procedures

JBailey — Tue, 02 Jan 2018 19:42:27 GMT

Hi @kiranv_

You are way too kind!

Best wishes,
Jeff