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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.