topic Re: Table constraints missing when creating a data set based on an oracle table in SAS Procedures

Table constraints missing when creating a data set based on an oracle table

jerry898969 — Thu, 10 Jun 2010 14:10:37 GMT

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

Re: Table constraints missing when creating a data set based on an oracle table

Doc_Duke — Fri, 11 Jun 2010 15:56:42 GMT

SAS does not support table constraints, so you can't extract it except as a text file.

Re: Table constraints missing when creating a data set based on an oracle table

jerry898969 — Mon, 14 Jun 2010 12:16:16 GMT

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

Re: Table constraints missing when creating a data set based on an oracle table

Doc_Duke — Mon, 14 Jun 2010 13:08:07 GMT

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

Re: Table constraints missing when creating a data set based on an oracle table

jerry898969 — Mon, 14 Jun 2010 13:21:02 GMT

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

Re: Table constraints missing when creating a data set based on an oracle table

bentleyj1 — Fri, 01 Feb 2013 21:20:00 GMT

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.