Primary key in SAS dataset

[deboomce]

Is it possible to define a primary key for a SAS dataset?
The use case is to append new data to an existing SAS dataset (which may be large; consider it an archive). There might be new data that is already in the archive, which can be checked by the unique ID column. Without having to read the entire archive in pandas (which is not efficient), how can I achieve this in SASPy?

[tomweber-sas]

Hey, so SAS uses indexes, which are separate files that which are associated to the SAS Data Set. These can be created a number of ways, and they can also have various constraints, which can match what a primary key is. Indexes can be created a number of ways, either when a data set is created or after the fact. This can be done using various syntaxes, via data step or procs, like proc datasets and and proc sql (proc sql uses syntax like 'primary key'). There's a good set of info here in the sections labeled Indexes and Integrity Constraints. SAS decides to use the indexes based upon how the data set is being processed. For instance, when WHERE clauses are used, or By Group processing is being done. For constraints like primary keys (which have to be unique and non-missing), the constraints are enforced when updating the dataset (appending, ...). So, it's really SAS that implements all of this, as opposed to SASPy having anything specific to do with it.

SASPy does provide ways to interact with some of what you may need. You can create a simple index using the outdsopts dict when creating a dataset via df2sd(). You can append to a dataset using the append() method of the SASData object. You can provide where clauses on the dsopts dict of a SASData object, or use the where() method. These things don't impose anything on how SAS processes the data, it still decides when to use an existing index or not, based upon the use case. But creating the indexes you want on a data set then allows SAS to process the data using those indexes when it is more efficient. Since there are so many different ways to define indexes, it's best to simply submit() whichever code you want to use to create them.

I'm not completely sure of your use case, but if you define a primary key on a SAS data set, then when you append to it, SAS will enforce that constraint and you can just append rows to it with no need to read it into pandas first.

Tom

[deboomce]

Thank you for the elaborate answer. Since SQL-based databases support uniqueness based on the primary key, I thought something similar would have been possible in SAS. This would imply that if I were to add a record to the table for which the primary key already exists, it would throw an error or simply ignore the newly inserted record.

[tomweber-sas]

Yes, in the first example here it shows creating a Primary Key (which has those same rules). In this example here it shows the error when trying to add a row with a duplicate key and some ways to track those rows. So, SAS does support Primary Key, like a database does.