BookmarkSubscribeRSS Feed
jerry898969
Pyrite | Level 9
I want to make a local copy of an oracle table. I need all the indexs, constraints, PK, etc..

When I do the following I just get the data without any of the constraints.

data tree_node ;
set ora.tree_node ;
run ;

What is the best way to get the exact table definition as it is within oracle ?

thank you
5 REPLIES 5
Doc_Duke
Rhodochrosite | Level 12
SAS does not support table constraints, so you can't extract it except as a text file.
jerry898969
Pyrite | Level 9
Doc,
Thank you so much for your response.

So when I try and create a data set based off of an oracle table or a different sas data set that have primary or foreign keys why doesn't those keys come along with the new data set?

Thank you
Doc_Duke
Rhodochrosite | Level 12
Jerry,

From the SAS SQL documentation, "The SQL research project at SAS has focused primarily on the expressive power of SQL as a query language. Consequently, some of the database features of SQL have not yet been implemented in PROC SQL."

To understand all the differences (there are lots), you will need to read all about SAS Datasets in the BASE documentation concepts area and the differences between SAS SQL and ANSI SQL.

Doc
jerry898969
Pyrite | Level 9
Doc,

Thank you I will look at the documentation.

I made the mistake of assuming since I was creating a data set based off of another data set that the PK and FK would carry along with it.

Thank You
bentleyj1
Quartz | Level 8

SAS does support integrity constraints but they do not come along with the data when you create a data set from an Oracle table.  They must be recreated using PROC DATASETS.  Here's a link to Mike Raithel's 2011 SGF paper.  It's a .pdf so just search on 'constraint'.  http://support.sas.com/resources/papers/proceedings11/274-2011.pdf

If the primary key and foreign key physically exist as fields in an Oracle table I don't know of any reason why they wouldn't be in the SAS data set, but there won't be anything other than the variable name to indicate that they are the PK and FK.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 750 views
  • 0 likes
  • 3 in conversation