topic Re: Schemas in SAS Programming

Schemas

HeatherNewton — Fri, 14 Oct 2022 09:08:01 GMT

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

Re: Schemas

Patrick — Fri, 14 Oct 2022 09:29:53 GMT

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

Re: Schemas

Ksharp — Fri, 14 Oct 2022 12:09:52 GMT

Something like this:

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

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

Re: Schemas

Tom — Fri, 14 Oct 2022 14:04:19 GMT

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

Re: Schemas

Reeza — Fri, 14 Oct 2022 15:25:06 GMT

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.

Re: Schemas

HeatherNewton — Sat, 15 Oct 2022 15:40:19 GMT

What is third party governance tool here?

Re: Schemas

Kurt_Bremser — Sat, 15 Oct 2022 17:33:24 GMT

This all falls under integrity rues and constraints. Such are usually implemented in database systems, like Oracle, DB/2, MySQL, MS SQL Server, Postgres, and many others.

Re: Schemas

Reeza — Sun, 16 Oct 2022 23:10:33 GMT

ER Studio/ERwin are usually used for storing ER models. There are many alternatives out of there and some DBs have them built in these days.

Re: Schemas

HeatherNewton — Mon, 17 Oct 2022 01:10:03 GMT

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?

Re: Schemas

Tom — Mon, 17 Oct 2022 03:27:02 GMT

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

Re: Schemas

Reeza — Mon, 17 Oct 2022 14:21:13 GMT

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.

Re: Schemas

HeatherNewton — Mon, 17 Oct 2022 17:37:23 GMT

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

Re: Schemas

SASKiwi — Mon, 17 Oct 2022 19:01:31 GMT

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

Re: Schemas

Reeza — Mon, 17 Oct 2022 19:24:42 GMT

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.

Re: Schemas

HeatherNewton — Tue, 18 Oct 2022 00:48:14 GMT

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

Re: Schemas

HeatherNewton — Tue, 18 Oct 2022 00:51:32 GMT

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

Re: Schemas

SASKiwi — Tue, 18 Oct 2022 01:53:03 GMT

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

Re: Schemas

Reeza — Tue, 18 Oct 2022 15:25:15 GMT

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?