SAS DI Error 22-232 in ROW_NUMBER () OVER (PARTITION BY construction

Question

I have 2 SQL Join transforms (one using only SELECT, the other using SELECT and WHERE) and I have syntax error near OVER when submitting the following code:

select row_number () over 
       (
         partition by t0.A order by t0.B DESC
       ) nub, 
       t0.C, 
       t0.D
from t0

I am new to SAS DI Studio, but this code works in Teradata, where am I going wrong?

Answer 1

Firstly, I can tell why this error is occurring, but it may fall to someone who has access to DI Studio to answer it fully for you. I've done my best to explain below. Feel free to comment.

The ROW_NUMBER() and PARTITION BY construct in Teradata SQL are not supported in SAS SQL. In order to accommodate this sort of syntax in DI Studio, you'll need to utilise 'pass-through' SQL. The syntax in Base SAS would be as follows:

PROC SQL;
  CONNECT TO TERADATA (/* Insert connection details */);
  CREATE TABLE sas_table AS
  SELECT * FROM CONNECTION TO TERADATA (
  /* Insert your Teradata SQL here */
  );
  DISCONNECT FROM TERADATA;
QUIT;

The syntax above will connect to Teradata and then create a SAS table called sas_table based on the results of your Teradata SQL query.

Dependent upon your version of DI Studio, there may be a specific transform available for Pass Through SQL.