If you use the query builder then SAS generates SAS SQL flavor code. During execution time SAS will attempt to translate this code to the DB flavor code and send as much of the logic as it can to the database for execution. SAS doesn't "know" that function F_CALC_REGISTRATION_HOURS() is an Oracle UDF and though won't send it to the database for execution - but then because the function doesn't exist on the SAS side your code will fail.
What you need is explicit SQL which instructs SAS to send all of your code "as is" to the database for execution.
Using the query builder you could try "Generate source for explicit pass-through" - this might or might not work (see SAS Note here). But even if it doesn't work it still should generate the code pattern for you.
Code like below should work.
proc sql;
connect to oracle as ora (<connection string>);
select * from connection to ora
(
SELECT
TBRACCD_PIDM
,TBRACCD_TERM_CODE
,BANINST1.F_CALC_REGISTRATION_HOURS(TBRACCD_PIDM,TBRACCD_TERM_CODE, 'TOTAL', 'CREDIT') AS Total_Credit_Hours
FROM TBRACCD
WHERE TBRACCD_BALANCE != 0
AND TBRACCD_TERM_CODE NOT IN ('ARTERM')
);
disconnect from ora;
quit;
If you've got already a valid libname to the Oracle schema defined then you can also use the libref in the connect statement as below (documented here).
proc sql;
connect using <SAS libref> as ora;
select * from connection to ora
(
SELECT
TBRACCD_PIDM
,TBRACCD_TERM_CODE
,BANINST1.F_CALC_REGISTRATION_HOURS(TBRACCD_PIDM,TBRACCD_TERM_CODE, 'TOTAL', 'CREDIT') AS Total_Credit_Hours
FROM TBRACCD
WHERE TBRACCD_BALANCE != 0
AND TBRACCD_TERM_CODE NOT IN ('ARTERM')
);
disconnect from ora;
quit;
... View more
