Describe table constraint -> output to dataset/macrovariable instead of saslog/report

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

Describe table constraint -> output to dataset/macrovariable instead of saslog/report

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]


Accepted Solutions
Solution
‎03-10-2016 06:02 AM
SAS Super FREQ
Posts: 709

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

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


All Replies
Occasional Contributor
Posts: 18

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

Hi,

 

Can you please check the same with proc contents.

 

Regards,

Abd.

Contributor
Posts: 48

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

[ Edited ]

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).

Super User
Super User
Posts: 7,980

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

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.?

Contributor
Posts: 48

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

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).

 

Super User
Super User
Posts: 7,980

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

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.

Solution
‎03-10-2016 06:02 AM
SAS Super FREQ
Posts: 709

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

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

Contributor
Posts: 48

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

Posted in reply to Bruno_SAS

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

SAS Super FREQ
Posts: 709

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

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

Super User
Super User
Posts: 7,980

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

Posted in reply to Bruno_SAS

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?

SAS Super FREQ
Posts: 709

Re: Describe table constraint -> output to dataset/macrovariable instead of saslog/report

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 667 views
  • 4 likes
  • 4 in conversation