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
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;
One for tech support I reckon. I couldn't find a syntax that worked as intended.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.