converting an Oracle table into a SAS data set while keeping the Integrity constraints

[alepage]

Hello,

I have developed with the help of peoples from the community, a SAS code to produce an Oracle table with all the comments and the integrity constraints as well.  When I use oracle and goes on the server, I can check that my Oracle table has the proper structure in terms of type of data, comments on columns and integrity constraints.

However, when the time comes to convert the Oracle table into a SAS Data set, the comments on columns are converted into variables labels which is fine but I am loosing the integrity constraints.

Has anyone ever managed to convert an Oracle table into a SAS data set while maintaining the integrity constraints?

I have tested those two methods and the integrity constraints are kept.

libname oralib oracle user=*** password=*** path=u.data;

/*Listing of the table available in oralib*/
proc datasets lib=oralib;
quit;

/*Using SAS/ACCESS engine *********/
data work.test1;
set oralib.D9_CADAS_CITYN_SRC;
run;
PROC CONTENTS DATA=TEST1;
RUN;
/*The PROC SQL Pass-Through Facility */

proc sql;
connect to oracle ( user=*** orapw=*** path=u.data);
create table Test2 as
select * from connection to oracle

( select * from D9_CADAS_CITYN_SRC );
disconnect from oracle;
quit;
PROC CONTENTS DATA=TEST2;
RUN;

[RW9]

Integrity constraints is an Oracle function.  Remember SAS is not the same as Oracle, they are two different systems with different requirements and ways of working.  You would be better off using the appropriate software for the appropriate part of the process, and simplifying the rest of the process down to just transferring of data when needed rather than trying to create database tables from SAS, then dumping out data from oracle etc.

[alepage]

I understand perfectly what you mean.  However, I looking at all the available options to carry out my duties.

regards,

Alain

[SASKiwi]

Integrity constraints are usually used to prevent orphan or invalid key values being added to tables during table updates. If your SAS table copy from Oracle is just read-only then integrity constraints are totally redundant. 

[LinusH]

Base SAS have integrity constraints. Bet they, like indexes, isn't migrated automatically when copying data. You need to specifiy it again in SAS.
But like @RW9 I'm not sure why you are doing this. Usually it's enough to have this in one place. You could elaborate why you are copying data from Oracle...

[alepage]

Hello everyone,

after many tries I finally found a way to do it.

Thank you for your comments.

[Patrick]

---

@alepage wrote:

Hello everyone,

after many tries I finally found a way to do it.

Thank you for your comments.

---

@alepage

Then please give back and share how you're doing it.

If it's a dynamic approach then I'd assume it's either via dictionary tables or pass-through SQL "pulling-out" the Oracle DDL and then applying it on the SAS side. But may-be you found another way and I could learn something new.