topic Re: Alternative to ROW NUMBER () in SAS DI in SAS Data Management

Alternative to ROW NUMBER () in SAS DI

amitvermajhs — Mon, 25 Jun 2018 10:21:27 GMT

Hi Team,

I am working on SAS DI, and using the user written code feature in it.

And getting the below error in it :

PROC SQL;
938
939 CREATE TABLE WNFTHD AS
940
941
942 /*create table rbm_m_CUSTPRODUCTSTATUS_ACTV_VLS AS */
943
944 SELECT
945 CUSTOMER_REF,PRODUCT_SEQ,PRODUCT_STATUS,STATUS_REASON_TXT,EFFECTIVE_DTM
946 FROM
947 (
948 SELECT
949 CUSTOMER_REF,PRODUCT_SEQ,PRODUCT_STATUS,STATUS_REASON_TXT,EFFECTIVE_DTM,
950 ROW_NUMBER() OVER (PARTITION BY CUSTOMER_REF,PRODUCT_SEQ ORDER BY EFFECTIVE_DTM DESC ) AS RN
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

951 FROM EBUSTG.MOB_CUSTPRODUCTSTATUS B
952 )P
953 WHERE
954 RN=1;

Does SAS DI, user written code does not support the row number () function if yes - what should be the alternative for that.

Re: Alternative to ROW NUMBER () in SAS DI

RW9 — Mon, 25 Jun 2018 10:54:42 GMT

Row_number() function is a proprietary compiled function by Oracle. It is not available in SAS SQL which is ANSI compliant and does not implement any third party functions. You can, if connecting to a database like oracle, pass through your SQL to the database to run, in which case the functions would be available per the database. Alternatively you would need to re-code your script, either by complicated SQL programming, or by using SAS language which is far simpler:

proc sort data=yourdata out=want;
  by customer_ref product_seq effective_dtm;
run;
data want;
  set want;
  retain row_num;
  if _n_=1 then row_num=1;
  if first.product_seq then row_num=row_num+1;
run;

Or something like that, haven't tested as nothing to test it on. Also avoid SHOUTING code.

Re: Alternative to ROW NUMBER () in SAS DI

Patrick — Mon, 25 Jun 2018 11:27:32 GMT

@amitvermajhs

You're using Oracle SQL which is totally fine IF your source table resides in Oracle AND you issue such code as explicit pass-through SQL (which you don't).

Re: Alternative to ROW NUMBER () in SAS DI

amitvermajhs — Mon, 25 Jun 2018 11:42:01 GMT

I have done all coding in SQL developer, and now need to automate the process in SAS DI. So using the user written code feature in SAS DI, to minimize the efforts. And all my codes have worked fine in SAS DI only the below code is giving error.

I have the source table in Oracle and need to use SAS DI codes to do the processing, guide me to have the alternative for below code , using sas dataset will be lengthy procedure as will need to create libraries

Till now I was using the below code piece to filter the variable:-

create table rbm_m_CUSTPRODUCTSTATUS_ACTV_VLS
AS
SELECT
CUSTOMER_REF,PRODUCT_SEQ,PRODUCT_STATUS,STATUS_REASON_TXT,EFFECTIVE_DTM
FROM
(
SELECT
CUSTOMER_REF,PRODUCT_SEQ,PRODUCT_STATUS,STATUS_REASON_TXT,EFFECTIVE_DTM,
ROW_NUMBER() OVER (PARTITION BY CUSTOMER_REF,PRODUCT_SEQ ORDER BY EFFECTIVE_DTM DESC ) AS RN
FROM MOB_CUSTPRODUCTSTATUS B
)P
WHERE
RN=1;

Re: Alternative to ROW NUMBER () in SAS DI

RW9 — Mon, 25 Jun 2018 12:11:58 GMT

Then you need to send the code to the database for actioning:

proc sql;
  connect to oracle (...);
  select * from connection to oracle (
    create table rbm_m_CUSTPRODUCTSTATUS_ACTV_VLS
      AS
      SELECT
CUSTOMER_REF,PRODUCT_SEQ,PRODUCT_STATUS,STATUS_REASON_TXT,EFFECTIVE_DTM
FROM
(
SELECT
CUSTOMER_REF,PRODUCT_SEQ,PRODUCT_STATUS,STATUS_REASON_TXT,EFFECTIVE_DTM,
ROW_NUMBER() OVER (PARTITION BY CUSTOMER_REF,PRODUCT_SEQ ORDER BY EFFECTIVE_DTM DESC ) AS RN
FROM MOB_CUSTPRODUCTSTATUS B
)P
WHERE
RN=1);
  disconnect from oracle;
quit;

I have just copied/pasted your code in there, good idea to apply some formatting to make it readable.

The connection to oracle, you need to put in schema, username, password information etc. I can't provide this to you:

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113595.htm

Re: Alternative to ROW NUMBER () in SAS DI

amitvermajhs — Mon, 25 Jun 2018 14:31:47 GMT

Thanks RW9, but have a query that -

1) My oracle table MOB_CUSTPRODUCTSTATUS is in schema EBU_STG --- so do I need to create a libname to access the table with below code

LIBNAME EBUSTG ORACLE encoding="utf-8" PATH=EBUP SCHEMA=EBU_STG AUTHDOMAIN="EBU_***_***_Domain" ;

2) In connect to oracle I just need to pass the username and pwd i.e. connect to oracle (user=*** password=****), or any other information needs to be passed

3) And in line - create table EBUSTG.rbm_m_CUSTPRODUCTSTATUS_ACTV_VLS --- do I need to provide any libname to guide the system which schema it should write the table.

Till now I was using the attached diagram in SAS DI to do the operations, and using a physical storage in user written code, and then the table loader to load the data in the dedicated table. Can I do the same with the code provided by you in the user written code.

Re: Alternative to ROW NUMBER () in SAS DI

RW9 — Mon, 25 Jun 2018 14:43:04 GMT

I am afraid you need to discuss that with your Oracle DB admin/helpdesk. I am not familiar with DI, nor would I open a zip file from the web. It needs someone who knows your systems and their interactions, you may already have connections setup for instance.

Re: Alternative to ROW NUMBER () in SAS DI

BrunoMueller — Mon, 25 Jun 2018 21:31:02 GMT

You can set the active schema after you created a connection to oracle, see this post for an example https://communities.sas.com/t5/Base-SAS-Programming/how-to-Alter-oracle-current-schema-via-sas-pass-through/td-p/435885

, see the post from @Tom

After that you can run your Oracle SQL using SQL Pass Through.

Re: Alternative to ROW NUMBER () in SAS DI

Patrick — Mon, 25 Jun 2018 23:08:31 GMT

@amitvermajhs

1. Amend your code to use <schema name>.<table name>

2. Execute the amended code in SQL Developer (best with a code window opened under a different schema)

3. If working copy/paste the code back to DI Studio into the code block for explicit SQL Pass-through

Please note:

IF you want the data transferred back to SAS then it's a select .... from connection to oracle ( <pass through code>);

IF it's only processing on the DB side (i.e. creating a table in Oracle) then it's in an execute block.

Do NOT use:

Create table <libref to Oracle.table name> .... select .... from connection to oracle ( <pass through code>); as this would likely first move the data from Oracle to SAS only to then move it back from SAS to Oracle to load the data into a newly created table in Oracle.

And last but not least:

DIS best practice is to only use user written code if you really can't implement the logic via standard transformation. Given that you just could copy/past your SQL into SAS SQL I would assume a lot of it could get implemented using the SQL Join transformation without any user written code.

The moment you use user written code you loose things like data lineage and full impact analysis capabilities.

Re: Alternative to ROW NUMBER () in SAS DI

amitvermajhs — Wed, 27 Jun 2018 08:14:29 GMT

Hi @RW9,

I tried the following code in user written code in SAS DI:-

LIBNAME EBUSTG ORACLE encoding="utf-8" PATH=*** SCHEMA=**** AUTHDOMAIN="EBU_STG_Auth_Domain" ;

proc sort data=EBUSTG.MOB_CUSTPRODUCTSTATUS out=WS58287;
by customer_ref product_seq effective_dtm;
run;
data WS58287;
set WS58287;
retain row_num;
if _n_=1 then row_num=1;
if first.product_seq then row_num=row_num+1;
run;

And getting the below error message

Table Loader
Line 1,323: WARNING: Variable row_num was not found on BASE file. The variable will not be added to the BASE file.

Line 1,323: ERROR: ROLLBACK issued due to errors for data set EBUSTG.MOB_CUSTPRODUCTSTATUS_ACTV_VLS.DATA.

Please assist on the same.

1) Firstly I am using the source dataset as 'EBUSTG.MOB_CUSTPRODUCTSTATUS' as this has the data

2) The out dataset is WS58287 as that is the physical storage name in the user define code

3) Getting the warning for Variable row_num was not found on BASE file. The variable will not be added to the BASE file.

4) I have the same table structure for source and destinations table i.e. 'EBUSTG.MOB_CUSTPRODUCTSTATUS' and EBUSTG.MOB_CUSTPRODUCTSTATUS_ACTV_VLS.

5) I have written the above code in user-written box --> connected it to table loader --> and finally connecting it to destination table i.e. EBUSTG.MOB_CUSTPRODUCTSTATUS_ACTV_VLS

6) I am attaching the SAS DI diagram in the the attachment DI.zip for reference

Please assist on the same

Re: Alternative to ROW NUMBER () in SAS DI

amitvermajhs — Wed, 27 Jun 2018 08:22:05 GMT

Hi @Patrick,

I have moved all the tables in a single schema, and the below code is working fine in SQL developer and giving me proper results and thus wants to replicate the below in SAS DI.

Please assist where should I write the below code in SAS DI, as till now I am using the Transformations --> Data --> User written code box to write the code and then use table loader to save it.

My sql code is as follows :-

Re: Alternative to ROW NUMBER () in SAS DI

RW9 — Wed, 27 Jun 2018 08:26:10 GMT

Please avoid using all uppercase in your code, it is shouting. Also please use the code window to post code - it is the {i} above post area.

You are accessing a table in a database = mob_custproductstatus, this table does not have a variable = row_num, hence you get an error.

What are you trying to do, you need to plan out your process. Will the data stay on the database, will it come into the SAS environment? If it remains on the database, then passthrough your original query to the database as you will then have access to Oracle functions. If not, then extract the data into SAS and use SAS to process the data. Currently you are trying to do both at the same time which will not work.

I would start by running this:

libname ebustg oracle encoding="utf-8" path=... authdomain=...;

proc sort data=ebustg.mob_custproductstatus out=ws58287;
  by customer_ref product_seq effective_dtm;
run;

Do you get the data in a dataset in library work? You should do, and be able to process that data using SAS. However that data wouldn't go back to the database as the table there has a different structure - i.e. you have added columns. It would work if you went to the database and updated the table structure there to include a new column called row_num, but I don't think that is the point of this process.

Re: Alternative to ROW NUMBER () in SAS DI

Patrick — Wed, 27 Jun 2018 09:20:31 GMT

@amitvermajhs

Here the docu link with examples as a starting point for you:

http://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=n05b4mygsvt845n1vnr6r5kchbjf.htm&docsetVersion=9.4&locale=en

If you want to stick with the code you've developed in SQL Developer then you need also to stick with user written code AND with Explicit Pass-Through.

That won't result in "best practice" SAS DI jobs (which should be metadata driven) but it's eventually the quickest and safest way for you to make things work.