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;
Some hints on the Teradata community here:
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).
28 proc sql;
30 connect to teradata (user=&ishid password=&ishpass tdpid="xxxxxx" connection=global mode=Teradata );
32 Execute (call DB.SP('2017-01-01','2017-01-03',XXX',ORC,OMSG));
ERROR 22-322: Expecting a name.
Can you Please Help me out that
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;
28 proc sql;
30 connect to teradata (user=&ishid password=&ishpass tdpid="XXXX" connection=global mode=Teradata );
32 Execute (Execute DB.SP( '2016-01-01', '2016-01-03' , 'XXX' ))by Teradata;
ERROR: Teradata execute: 'DB.SP' is not a macro.
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;
I have taken a shot at explaining this via a SAS Communities article.
Let me know what you think.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.