Primary key

[Ronein]

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

[RW9]

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.

[s_lassen]

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.

[LinusH]

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.

[Patrick]

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