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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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