SAS getting table primary key

Question

I'm completely new in SAS 4GL...

Is it possible to extract from table, which columns are primary key or parts of compound primary key? I need their values to be merged into one column of an output dataset.

The problem is, that as an input I can get different tables, and I don't know theirs definition.

score 4 · Answer 1 · answered Mar 31 '14 at 13:50

If an index is defined, then you can find out what variable(s) is/are used in that index. See for example:

data blah(index=(name));
set sashelp.class;
run;

proc contents data=blah out=blahconts;
run;

blahconts has columns that indicate that name is in a simple index, and that it has 1 index total.

Also, you can have foreign key contraints, such as the following from this SAS documentation example:

proc sql;
   create table work.mystates
      (state      char(15), 
       population num,
       continent  char(15),

          /* contraint specifications */
       constraint prim_key    primary key(state),
       constraint population  check(population gt 0),
       constraint continent   check(continent in ('North America', 'Oceania')));      

   create table work.uspostal
      (name      char(15),
       code      char(2) not null,          /* constraint specified as    */
                                            /* a column attribute         */

       constraint for_key foreign key(name) /* links NAME to the          */
                  references work.mystates   /* primary key in MYSTATES    */

                     on delete restrict     /* forbids deletions to STATE */
                                            /* unless there is no         */
                                            /* matching NAME value        */

                     on update set null);   /* allows updates to STATE,   */
                                            /* changes matching NAME      */
                                            /* values to missing          */ 
quit;

proc contents data=uspostal out=postalconts;
run;
proc sql;
describe table constraints uspostal;
quit;

That writes the constraint information to the output window. From the output dataset you can see that the variable is in a simple index. You can wrap either of these (the PROC CONTENTS or the DESCRIBE TABLE CONSTRAINTS) in ODS OUTPUT to get the information to a dataset:

ods output  IntegrityConstraints=postalICs;
proc contents data=uspostal out=postalconts;
run;
ods output close;

or

ods output  IntegrityConstraints=postalICs;
proc sql;
describe table constraints uspostal;
quit;
ods output close;

SAS getting table primary key

1 Answers1