topic Re: Asking the experts / how to find the proper key between two sas or oracle table in SAS Enterprise Guide

Asking the experts / how to find the proper key between two sas or oracle table

alepage — Thu, 19 Apr 2018 14:53:19 GMT

Hello,

We have very little documentation on the tables available on the company server.

Moreover, we are asked to create new table using proc sql create table as

and to achieve that we need to know the primary key of each table.

For example:

proc sql;

create table NewTable as

select A.*, B.customerNumber, B.phonenumber

from Dataset1 A

left join Dataset2 B

on A.primarykey = B.primarykey

;

Quit;

However, it becomes difficult to know which variables could be used as corresponding key number.

So my Question is:

Does it exist a program or a procedure to find the best key between two tables.

Regards,

Re: Asking the experts / how to find the proper key between two sas or oracle table

Sven111 — Thu, 19 Apr 2018 20:24:47 GMT

I generally just use my Mk1 Eyeball and trial and error. It's usually pretty obvious with most datasets what the primary and/or foriegn keys are or could be if they were setup halfway decent. Sorry this isn't much help though.

Re: Asking the experts / how to find the proper key between two sas or oracle table

alepage — Fri, 20 Apr 2018 01:36:54 GMT

I know that it is simple to find the primary key of a dataset but it is not so simple to find the good keys between two datasets for which we have little information.

regards

Re: Asking the experts / how to find the proper key between two sas or oracle table

SASKiwi — Fri, 20 Apr 2018 23:02:44 GMT

With Oracle tables you can examine the table schemas to see if physical, surrogate and foreign keys are set up on any columns. These may help in deciding what columns to join on.

SAS tables don't have the equivalent of physical keys, but can have indexes and constraints applied to columns. In my experience these are not heavily used so the eyeball approach as mentioned by @Sven111 is your best bet.

Re: Asking the experts / how to find the proper key between two sas or oracle table

Patrick — Sat, 21 Apr 2018 05:58:03 GMT

@alepage

If you've got the SAS DataFlux Data Management Studio licensed and available then you could use the data profiling task which also does primary key and foreign key analysis.

https://support.sas.com/documentation/cdl/en/dmov/68252/PDF/default/dmov.pdf

Re: Asking the experts / how to find the proper key between two sas or oracle table

alepage — Tue, 24 Apr 2018 12:08:38 GMT

Does SAS DataFlux Data Management is available with SAS enterprise guide 7.15?

Re: Asking the experts / how to find the proper key between two sas or oracle table

SASKiwi — Tue, 24 Apr 2018 22:52:40 GMT

No, it's a separate product that has its own user interface and there would be a licensing cost. It is available as a standalone workstation product or as part of a SAS Data Quality server-based solution.

If it isn't available to you now, then getting a new product just to meet a narrow need would not be a good choice in my opinion.