BookmarkSubscribeRSS Feed
alepage
Barite | Level 11

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;

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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

regards,

Alain

SASKiwi
PROC Star

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
Tourmaline | Level 20
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...
Data never sleeps
alepage
Barite | Level 11

Hello everyone,

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

Thank you for your comments.

Patrick
Opal | Level 21

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1267 views
  • 0 likes
  • 5 in conversation