topic Re: How do I retrieve index names of a table and the columns used for those indexes. in SAS Programming

How do I retrieve index names of a table and the columns used for those indexes.

Longimanus — Fri, 10 Nov 2023 10:36:25 GMT

Hi to y'all. I have surfed the web and got the PROC CONTENTS tip with a VERBOSE option. For some reason (I do not understand) I cannot use this:

32 proc contents data=sashelp.class (verbose=yes);
-------
ERROR 22-7: Invalid option name VERBOSE.

If I just run PROC CONTENTS on another table and print the result I get this e.g.:

                                 The CONTENTS Procedure                                      
                                                                                                  
                            Alphabetic List of Indexes and Attributes                             
                                                                                                  
                                                  # of                                            
                                   Unique       Unique                                            
            #    Index             Option       Values    Variables                               
                                                                                                  
            1    CIP_COUNTRY_CD                      4                                            
            2    PRIM_KEY          YES       129552652    ACCOUNT_RK VALID_FROM_DTTM

How do I get this information in a table? If I use the OUT= option to a table, I can see the variables used in the indexes, if they are a simple or part of a composite key. But niether the sequence of the vars in the composite nor the name of the index.

This should be pretty basic and yet I cannot find it surfin' the web. I hope someone can help!

Cheers from Copenhagen!

Menno

Re: How do I retrieve index names of a table and the columns used for those indexes.

ErikLund_Jensen — Fri, 10 Nov 2023 11:12:35 GMT

Hi @Longimanus

You can retrieve the wanted information from the sashelp.vindex table.

Note that library names and member names are uppercase in the dictionary tables, so you need to use "where libname = upcase(yourlib) and memname = upcase(yourds);.

cheers from Odense.

Re: How do I retrieve index names of a table and the columns used for those indexes.

Longimanus — Fri, 10 Nov 2023 11:39:45 GMT

Tak Erik! Det vil jeg se på med det samme! 😊 (And for the non Danish speakers: Thank you Erik! I will look at that (and use it) right away! 😊