BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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

 

 

 

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

 

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
Meteorite | Level 14

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
Tourmaline | Level 20

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
Patrick
Opal | Level 21

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 15424 views
  • 1 like
  • 5 in conversation