Calling Oracle User-Defined Functions in SAS EG

[dobby]

I want to call an Oracle user-defined function within SAS EG. The function is to be called as a column output from a table and uses parameters from the said table as well. I tried to do it as a computed column but SAS EG said it wasn't a valid. I would normally call the function from SQL as follows:

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')

I pulled in TBRACCD from my library and used Query Builder to select the columns I want as output and add the necessary filters but just can't get the function call in. Is it possible from SAS EG?

[SASKiwi]

Try using SAS code. The EG Query Builder will only go so far with database-specific features.

Try adding your query into something similar to this:

libname myora oracle datasrc=datasource schema = schema user=user password = password;

proc sql;
 connect using myora;
  create table Want as 
  select * from connection to myora
  (<-Put your Oracle query here->)
  ;
quit;

[Patrick · Accepted solution]

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;