SAS Dictionary.columns PK

[JJP1]

i wanted to check on datasets whether it has primary key or not.
and iam running dictionary.columns dataset using where options libname and memname.
How would i check whether the dataset has primiary key or not.
i can see on column "notnull" ,in it iam seeing only values as "no".
can i consider this as primary key ? i don't think i can consider this..
any help on this please

[ed_sas_member]

Hi @JJP1 

Here is a way to create a primary key:

proc datasets library=work nolist;
	modify mydata;
	ic create name=primary key(myvariable); /*Define variable 'myvariable' as primary key*/
run;

... and check whether a primary key been defined or not:

proc sql;
	describe table constraints work.mydata;
quit;

Maybe the following paper would be interesting for you: https://www.lexjansen.com/wuss/2010/databases/3001_5_DDI-Thornton.pdf

Hope this helps!

Best,

[BrunoMueller]

You can use Proc CONTENTS or use some of the dictionary tables avaialble with Proc SQL to display information on contraints defined for a SAS data set. Have a look at the code example below. See here for a listing of available dictionary tables https://go.documentation.sas.com/?docsetId=sqlproc&docsetTarget=n02s19q65mw08gn140bwfdh7spx7.htm&doc...

data myClass;
  my_pk = _n_;
  set sashelp.class;
run;

proc  sql;
  alter table myclass
    add primary key (my_pk)
  ;
quit;

ods trace on;
ods output IntegrityConstraints=my_ic;
proc contents data=work.myclass;
run;
ods trace off;

proc sql;

create table TABLE_CONSTRAINTS as
select * from dictionary.TABLE_CONSTRAINTS
where table_catalog = "WORK" and table_name = "MYCLASS"
;

create table CONSTRAINT_COLUMN_USAGE as
select * from dictionary.CONSTRAINT_COLUMN_USAGE 
where table_catalog = "WORK" and table_name = "MYCLASS"
;

create table CONSTRAINT_TABLE_USAGE as
select * from dictionary.CONSTRAINT_TABLE_USAGE
where table_catalog = "WORK" and table_name = "MYCLASS"
;

create table columns as
select * from dictionary.columns
where libname = "WORK" and memname = "MYCLASS"
;
quit;

[JJP1]

@BrunoMueller ,i checked manually sashelp.class whether it has any PK,but it did not show any.

And i can see that we are adding PK manually using alter command.sorry to trouble.i did not clearly understood please.

would you please help

[BrunoMueller]

The example first creates a SAS data set with a primary key, and then shows how the output of Proc CONTENTS for this SAS data set, along with several queries against different dictionary tables and how this primary key shows up in this dictionary tables.

So you can use the queries in the example to get all SAS data sets with primary key definitions, just change the where clause as necessary

[ballardw]

I think that you want to look at the variable IDXUSAGE in dictionary.columns. Any value there will tell whether a variable is part of an index. SAS doesn't really use "primary key" but indexes basically serve the same purpose. The main issue if a composite index, two or more variables, are involved you wont have information about hierarchy or such.

The NOTNULL is only going tell that a variable can't be missing which is not a sufficient criteria to determine if it is part of a key.

[JJP1]

Thanks @ballardw ,I can see under indexusage column with two values "Simple" & "Composite"  for datasets.

but if i want to see what is simple or composite indexes-i need to write manually proc contents option.Would you please help  how can i check this with easy option please

[ballardw]

---

@JJP1 wrote:

Thanks @ballardw ,I can see under indexusage column with two values "Simple" & "Composite"  for datasets.

but if i want to see what is simple or composite indexes-i need to write manually proc contents option.Would you please help  how can i check this with easy option please

---

I'm not sure what you are asking here. You could filter the SQL result to only include records where the INDEXUSAGE is not missing.

And what proc contents would you need to write???

[Tom]

You can use ODS output to get any text that PROC CONTENTS prints into datasets. Use ODS TRACE ON to see what the outputs are.

https://blogs.sas.com/content/iml/2017/01/09/ods-output-any-statistic.html

[Tom]

Why?