topic Re: Using OR_IDENTITY_COLS in SAS 9.4 in SAS Programming

Using OR_IDENTITY_COLS in SAS 9.4

tborange — Tue, 18 Dec 2018 20:26:28 GMT

Greetings,

I'm on SAS 9.4 and trying to use OR_IDENTITY_COLS to Use Oracle Sequence when loading a table. The documentation @ https://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=n17merknv57i0un1vhszwcwbzab9.htm&docsetVersion=9.4&locale=en is vague.

Has anyone used this. If so, can you provide an example

Thanks

ChrisNZ — Tue, 18 Dec 2018 22:45:40 GMT

One for tech support I reckon. I couldn't find a syntax that worked as intended.

tborange — Tue, 18 Dec 2018 22:52:44 GMT

HI Chris,

Thanks for your opinion.

I was able to get it to work syntactically. However, the sequence # didn't flow through to the Oracle table.

I'll contact my company's tech support rep and update this thread accodringly.

Thanks again.

Troy

tborange — Thu, 03 Jan 2019 20:46:37 GMT

SAS Tech Support provided the answer:

Note: That if you're using another Oracle schema's sequence, you need to add it as follows.

'{Schema Name Here}.TEST_SEQ.NEXTVAL'

Here is an example:

DATA TEST_IMPORT;

INPUT LOC_NM $;

DATALINES;

EIN

BVN

TST

RUN;

*NWDWLIB is a macro variable with the string of connecting to one of our Oracle DBs;

LIBNAME &NWDWLIB;

PROC SQL;

INSERT INTO NWDWLIB.TEST_LOC

(OR_IDENTITY_COLS=(DW_TEST_LOC_ID='TEST_SEQ.NEXTVAL'),DW_TEST_LOC_ID,LOC_NM)

SELECT ., LOC_NM

FROM WORK.TEST_IMPORT;

QUIT;

LIBNAME NWDWLIB CLEAR;