BookmarkSubscribeRSS Feed
david999
Calcite | Level 5

I'm trying to call a stored procedure on redshift from SAS, and I am running into the below error. Could someone please help me with this?

 

PROC SQL noerrorstop;
connect to SASIORST( READBUFF=10000 DSN=DBSTG USER=sas PASSWORD="******");
create table work.test as
select * from connection to SASIORST
(call mccm.proc_temp);
disconnect from SASIORST;
quit;

 

ERROR: CLI prepare error: [SAS][ODBC Redshift Wire Protocol driver][Redshift]ERROR: syntax error at end of input(Position 20; File
/home/ec2-user/padb/src/pg/src/backend/parser/parser_scan.l; Line 711; Routine yyerror; )
SQL statement: call mccm.proc_temp.

1 REPLY 1
SASKiwi
PROC Star

External database stored procedures have to be run using the EXECUTE statement:

PROC SQL noerrorstop;
connect to SASIORST( READBUFF=10000 DSN=DBSTG USER=sas PASSWORD="******");
execute (call mccm.proc_temp) by SASIORST;
disconnect from SASIORST;
quit;

However you can't read a result set from EXECUTE. If the stored procedure is producing a result set then it needs to be changed so a table or view is created instead. Then this table can be read in a following SELECT statement.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Autotuning Deep Learning Models Using SAS

Follow along as SAS’ Robert Blanchard explains three aspects of autotuning in a deep learning context: globalized search, localized search and an in parallel method using SAS.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1603 views
  • 0 likes
  • 2 in conversation