Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

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

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,

 


Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 3,761

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

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.

View solution in original post


All Replies
Frequent Contributor
Posts: 97

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

[ Edited ]

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.

Frequent Contributor
Posts: 111

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

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

 

 

Super User
Posts: 3,761

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

[ Edited ]

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.

Respected Advisor
Posts: 4,536

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

@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 

Frequent Contributor
Posts: 111

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

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

Solution
4 weeks ago
Super User
Posts: 3,761

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 189 views
  • 0 likes
  • 4 in conversation