Schemas

[HeatherNewton]

I have lots of sas datasets. We are about to create schemas in oracle. How can i get the schemas from sas, seems proc content do not already identify primary keys foreign keys

[Patrick]

Oracle schemas are - very loose comparison - more like your folders where you store tables.

Proc Contents shows you what has been physically implemented.

With SAS tables most of the time PK's, indexes and even more so foreign key constraints don't get physically implemented.

What to physically implement in Oracle needs planning/design, will need scripting (DDL's) and you need of course to know your data and the relationships between the tables (data model).

[Ksharp]

Something like this:

proc sql;
connect to odbc(dsn= pw= .........);

select *
from connection to odbc(ODBC::SQLTables);
quit;

[Tom]

---

@HeatherNewton wrote:
I have lots of sas datasets. We are about to create schemas in oracle. How can i get the schemas from sas, seems proc content do not already identify primary keys foreign keys

---

That is because you generally do not need to worry about such things with a SAS dataset.

If you want to define primary keys etc in your Oracle database then you will need to do that yourself.

If you need help guessing what might be a key you might want to check out the NLEVELS option on PROC FREQ.

proc freq data=mylib.mydataset nlevels;
   tables _all_ / noprint;
run;

If something has the same number of levels as there are observations in the dataset then it is a good candidate for a key.

Example:

proc freq data=sashelp.class nlevels;
 tables _all_ / noprint;
run;
proc contents data=sashelp.class varnum ;
run;

Looks like NAME is the primary key for SASHELP.CLASS.

[HeatherNewton]

Thanks Tom. What if no such variables exist, ( ie proc freq level n not equal or close to obersatiin from proc content?

Moreover how to deal with foreign keys?

[Tom]

---

@HeatherNewton wrote:
Thanks Tom. What if no such variables exist, ( ie proc freq level n not equal or close to obersatiin from proc content?

Moreover how to deal with foreign keys?

---

Why would you need to define the foreign keys?  You didn't need them when you were using the data with SAS.

If you are starting a new project that will work with this data in some other database tool then follow the normal processes you use to build a database in that tool.  Just because you already have the data (or at least the starting point of the data) probable does not really change that process.

[Reeza]

Foreign keys are more of a concept than a physical construct They tell you how the join the data but they don't affect the data. You still have access to the variables and the values. You can use them in the analysis and joins the same way you would in a DB.

[HeatherNewton]

If i am to create tables in Oracle, what do I really need?

[SASKiwi]

If you don't have anyone creating an Oracle data model for you to load into, ask your Oracle DBA for advice. 

[Reeza]

The correct access privileges?

It depends on type of scope. Are you creating tables for others to use that require foreign keys/schemas? For yourself/team to maintain processes?
I would create tables using SAS SQL Pass through to create the tables and then insert using SAS libraries/steps as needed. You can create foreign keys using SQL Pass through and primary keys in SAS directly.

[HeatherNewton]

A dba creating for us with correct access privileges. Then my team will maintian. What is sql pass through?

[SASKiwi]

SQL Pass Through: SAS Help Center: How the SQL Pass-Through Facility Works

[Reeza]

What does maintain mean to you?
Insert, update, and/or delete records?

If so, all of that is doable from Base SAS with a libname connection. Treat them as SAS datasets and you should be fine. 

However, do test how date and datetimes work and ensure you're aware of how to update/insert dates.

---

@HeatherNewton wrote:
A dba creating for us with correct access privileges. Then my team will maintian. What is sql pass through?

---

[HeatherNewton]

We are creating table in orAcle then the transformation we use data stage and may be oracle if required.

[Reeza]

You have two different concepts in there, schemas and primary keys.

Schema is more like SAS library that helps you organize your data into subject matter domains.

Primary Keys help with lookup and joins and indexes (if created). Primary Keys are not a concept really recognized in SAS and you usually use a third party data governance tool to look at your data models.