BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alepage
Barite | Level 11

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

6 REPLIES 6
Sven111
Pyrite | Level 9

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.

alepage
Barite | Level 11

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

 

 

SASKiwi
PROC Star

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.

Patrick
Opal | Level 21

@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 

alepage
Barite | Level 11

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

SASKiwi
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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