Help using Base SAS procedures

Regarding SAS Stored Procedures

Reply
Occasional Contributor
Posts: 6

Regarding SAS Stored Procedures

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',?,?);
 
 

 

Community Manager
Posts: 3,424

Re: Regarding SAS Stored Procedures

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

PROC Star
Posts: 508

Re: Regarding SAS Stored Procedures

[ Edited ]

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

 

Occasional Contributor
Posts: 6

Re: Regarding SAS Stored Procedures

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 

Occasional Contributor
Posts: 6

Re: Regarding SAS Stored Procedures


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

PROC Star
Posts: 508

Re: Regarding SAS Stored Procedures

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;

 

 

Community Manager
Posts: 3,424

Re: Regarding SAS Stored Procedures

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;
Occasional Contributor
Posts: 6

Re: Regarding SAS Stored Procedures

Posted in reply to ChrisHemedinger

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.

Community Manager
Posts: 3,424

Re: Regarding SAS Stored Procedures

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;
SAS Employee
Posts: 279

Re: Regarding SAS Stored Procedures

Hi @kiranv_

 

You are way too kind!

 

Best wishes,
Jeff

SAS Employee
Posts: 279

Re: Regarding SAS Stored Procedures

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

Ask a Question
Discussion stats
  • 10 replies
  • 280 views
  • 4 likes
  • 4 in conversation