DATA Step, Macro, Functions and more

Primary key

Reply
Regular Contributor
Posts: 200

Primary key

Hello ,

I know that there is a way to create a primary key to a table using proc sql.

for example:

PROC SQL;
Alter table aaa
Add primary key(ID,month);
QUIT;

 

is there a way to add a primary key to a data set by using sas data set and not proc sql?

What is the advantage of adding a primary key to a table?

Usually when I see SAS programs I don't see that people adding primary key to data sets.

But in sql server I usually see that people add primary key to tables.

Why?

 

 

thanks

R

 

 

 

 

Super User
Super User
Posts: 9,855

Re: Primary key

 

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000247691.htm

 

Primary keys can speed up merges/joins - which as a database (RDBMs) does a lot it will be used a lot.  Most SAS programming is stepwise, so less joining.  Never actually used a key in SAS in years.  This is just an opinion though, if you use proc sql a lot, connect to databases etc. then you will need it more I would imagine.

 

PROC Star
Posts: 277

Re: Primary key

SAS does not have the concept of primary keys. But you can add a unique index, which amounts to about the same:

proc sql;
  create unique index prim_key
  on aaa(ID,month);
quit;  

The only difference being that unique keys do not require the columns to be NOT NULL. You will have to add constraints for that separately, if you want it.

Super User
Posts: 5,919

Re: Primary key

Actually, SAS has PK/FK constraints for Base data sets:

https://communities.sas.com/t5/Base-SAS-Programming/Primary-key/m-p/456421

 

Create in data step - no, PROC DATASETS, SQL and SCL (!) is the only ways to define them.

 

Why use them: apart from potential performance gains during join, they obviously cater for referential integrity - helps you maintaining your data model intact.

 

Why not so often in SAS - that's perhaps because RDBMS and SAS often has different use cases, whereas SAS is more flexible and users have more "power". But if you wish to build a governed solution with structured ETL and so forth, PK/FK could definitely be part of the design even in SAS.

 

Data never sleeps
Respected Advisor
Posts: 4,797

Re: Primary key

@Ronein

The info under below link might give you a lot of the information you're after.

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=n06cy7dznbx6gen1q9mat8de6rdq.htm&docsetVer...

Ask a Question
Discussion stats
  • 4 replies
  • 477 views
  • 1 like
  • 5 in conversation