04-23-2018 04:08 AM
I know that there is a way to create a primary key to a table using proc sql.
Alter table aaa
Add primary key(ID,month);
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.
04-23-2018 04:19 AM
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.
04-23-2018 04:22 AM
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.
04-23-2018 07:52 AM
Actually, SAS has PK/FK constraints for Base data sets:
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.
04-23-2018 05:00 AM
The info under below link might give you a lot of the information you're after.