How to call a stored procedure on Redshift from SAS?

[david999]

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.

[SASKiwi]

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.