Using OR_IDENTITY_COLS in SAS 9.4

[tborange]

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&doc... is vague.

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

Thanks

[ChrisNZ]

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

[tborange]

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 · Accepted solution]

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;