topic Re: Primary key in SAS Programming

Primary key

Ronein — Mon, 23 Apr 2018 08:08:18 GMT

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

Re: Primary key

RW9 — Mon, 23 Apr 2018 08:19:02 GMT

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.

Re: Primary key

s_lassen — Mon, 23 Apr 2018 08:22:38 GMT

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.

Re: Primary key

Patrick — Mon, 23 Apr 2018 09:00:05 GMT

@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&docsetVersion=9.4&locale=en

Re: Primary key

LinusH — Mon, 23 Apr 2018 11:52:15 GMT

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.