BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Edoedoedo
Pyrite | Level 9

Hi,

I have a dataset, and I need to know with code if such a dataset has a primary key constraint defined, and if so which field are there in the primary key.

I tried using "describe table constraints" sql-statement, but it just write the result in the log and in a report. Instead, I need to have the result in a dataset or in a macrovariable in order to process the result with code.


Can you help me?

Thanks a lot

[SAS9.4M2]

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

Hi

 

Use the OUT2= option with Proc CONTENTS, this will give the information on the constraints you.

 

You can also use the ODS OUTPUT statement to route the printed result to a SAS Data set, the ODS TRACE ON; statement will show you which output objects are created by a proc.

 

See a code sample below:


ods trace on;
ods output IntegrityConstraints=ic;
proc contents data=testali out2=info;
run;
ods trace off;

Bruno

View solution in original post

10 REPLIES 10
ad123123
Fluorite | Level 6

Hi,

 

Can you please check the same with proc contents.

 

Regards,

Abd.

Edoedoedo
Pyrite | Level 9

I did, but all I get is that in the "IDXUSAGE" field, the primary key fields are marked with "SIMPLE" or "COMPOSITE", and the other fields are marked with "NONE". So in this way I just know that there are some fields which are part of a simple/composite index, but I have no information about if such a index satisfy also the primary key constraint (i.e. unique not null).

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Could you provide a small code example of what you mean?  All of the information has to be held in one of the SASHELP.Vxyz views somewhere, the describe just outputs this information, much like contents.  Have you looked in all the tables VTABLE/COLUMN etc.?

Edoedoedo
Pyrite | Level 9

Hi,

for instance I have:

data TESTALI; a=0; b=0; c=0; d=0; e=0; run; 

proc sql; 
ALTER TABLE TESTALI ADD CONSTRAINT UPK PRIMARY KEY (a,b,c); 
quit;

I'd like to generate from this a dataset like:

LIBNAME | DATASET | CONSTRAINT NAME | CONSTAINT TYPE | FIELD

WORK | TESTALI | UPK | PRIMARY KEY | a

WORK | TESTALI | UPK | PRIMARY KEY | b

WORK | TESTALI | UPK | PRIMARY KEY | c

 

I'm trying to access sashelp.vcolumn with:

 

proc print data=sashelp.vcolumn ; where libname = 'WORK'; run ;

 

but unfortunately it is taking so long that I can't even know what it contains in a test (I'm in a production environment with dozen of preassigned libnames linked to teradata dbms with hundreds of tables in each).

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, this should give you the result:

data want (keep=libname memname name idxusage);
  set sashelp.vcolumn (where=(libname="WORK" and memname="TESTALI" and idxusage="COMPOSITE"));
run;

You will then have a dataset you can print or use further, there's not really a faster way of doing it, and if this is too slow I would ask why you have so much metadata (as this should take no more than a minute or two for thousands of tables), you may have things in there you don't need - i.e. db link links all table in the database etc.

BrunoMueller
SAS Super FREQ

Hi

 

Use the OUT2= option with Proc CONTENTS, this will give the information on the constraints you.

 

You can also use the ODS OUTPUT statement to route the printed result to a SAS Data set, the ODS TRACE ON; statement will show you which output objects are created by a proc.

 

See a code sample below:


ods trace on;
ods output IntegrityConstraints=ic;
proc contents data=testali out2=info;
run;
ods trace off;

Bruno

Edoedoedo
Pyrite | Level 9

Thanks a lot, it indeed works exactly as I need!

@RW9 It works as well! the problem is the speed, personally I haven't defined any libname (yet), but the program I'm writing needs to be run in EG, and the EG gets dozen of preassigned libraries upon connection which are directly mapped with the teradata engine, and each libname (schema in teradata) contains hundreds of tables; so I guess there is anything I can do to speedup access to sashelp.v*, such preassigned libnames are defined by the admin and are currently used by other user so they can't be removed...

 

Thank you very much.

Greetings

BrunoMueller
SAS Super FREQ

Hi

 

Whenever possible I recommend to write an SQL query to go against the DICTIONARY tables in Proc SQL. It seems to be faster than using the SASHELP.V.. views.

 

See an example below:

proc sql;
  create table want2 as
  select
    libname
    , memname
    , name
    , idxusage
  from
    dictionary.columns
  where
    libname = "WORK"
    and upcase(memname) = "TESTALI"
    and idxusage="COMPOSITE"
  ;
quit;

Bruno

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, I have a theory on that.  Since moving to 9.4, could have been in 9.3 too, I note that the metadata views take a long time, and this seems to have something to do with tables, there are lots of them.  The other theory is the mix of 32bit and 64bit datasets, a lot of my datasets are from 32bit, and when trying to access metadata the log shows a lot of cross environment data access.

 

A question you may be able to help, why would a proc contents be any quicker than a datastep?  It still needs an open, where clause, and then it would need to generate output.  The datastep is just open, where, close?

BrunoMueller
SAS Super FREQ

I guess the main difference is that Proc CONTENTS will read the desriptive part of a SAS data set directly (only one data sets). Whereas the SASHELP.V... and the DICTIONARY tables potentially have to deal with several tables and use a "longer" way till they get the information.

 

Bruno

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
  • 10 replies
  • 3084 views
  • 4 likes
  • 4 in conversation