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;
To use a Teradata Stored Procedure (or any DBMS Stored Procedure) you need to do it as a CALL EXECUTE, and the results would be a Teradata temporary table (or Global Temporary table, or some kind of scratch table). You would then use Create table work.sasresult as Select * from connection ... to read that table into SAS.
proc sql;
connect to TERADATA
(
SERVER=ABCDE AUTHDOMAIN="ABC12345"
);
CREATE TABLE WORK.ABCD_AP_LDC_010_V AS ----<<< CREATES A WORK.TABLE IN SAS
select *
from connection to TERADATA
(
SELECT * FROM TERADATA_SCHEMA.ABCD_AP_LDC_010_V
);
execute (commit) by TERADATA;
execute (
CREATE VOLATILE TABLE ABCD_AP_LDC_010_V AS ----<<< CREATES A VOLATILE TEMPORARY TABLE IN TERADATA
(SELECT TOP 5 AB_ID
FROM
TERADATA_SCHEMA.ABCD_AP_LDC_010_V) WITH DATA
ON COMMIT PRESERVE ROWS
) by TERADATA;
execute (commit) by TERADATA;
execute(TERADATA_SCHEMA.STORED_PROCESS(PARAMETERS)); -----<<< TO EXECUTE A STORED PROCESS/MACRO
disconnect from TERADATA;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.