topic Re: Extract PK from Phys. table, load it to new table. in SAS Procedures

Extract PK from Phys. table, load it to new table.

Yura2301 — Mon, 29 Sep 2014 09:18:48 GMT

Hi ,

Stuck on simple task -just write columns belongs to tables PK to separate phisical(not metadata) table.

I tried a few codes but always got same error, f.e.:

ods _all_ close;

ods output IntegrityConstraints=outputPK;

proc contents data=&tableName ;

run;

ods output close;

Waring put's to the log:

WARNING: Output 'IntegrityConstraints' was not created. Make sure that the output object name, label, or path is spelled

correctly. Also, verify that the appropriate procedure options are used to produce the requested output object. For

example, verify that the NOPRINT option is not used.

But "IntegrityConstraints" should be right name for output table:

http://support.sas.com/documentation/cdl/en/odsug/61723/HTML/default/viewer.htm#a002649072.htm

Same results when I used "describe table constraints" to get PK.

When I used "Attributes" instead of "IntegrityConstraints" - code worked ok and output table,as expected.

If there is more clever approach to get PK from physical table - please share, the code should work as quick as possible and return only list of PK columns.

Thanks!

Re: Extract PK from Phys. table, load it to new table.

RW9 — Mon, 29 Sep 2014 09:34:48 GMT

Hi,

Well, that link isn't working in my Internet Explorer (no suprises there). I would suggest that you put before your procedure:

ods trace on;

This will output to the log all selectable tables that the procedure creates when it runs. I have done this on 9.3 and see only EngineHost, Varibles, SortedBy as selectable tables from that procedure, hence you get an Error saying IntegrityConstraints not created. I don't know where you go the ide from?

Re: Extract PK from Phys. table, load it to new table.

Yura2301 — Mon, 29 Sep 2014 09:51:24 GMT

Hi,

Strange that you can't open the link, anyway it's just list of ods output tables,topic name "ODS Output Object Table Names".

Thanks for the tip with trace on, it shows that there are really no table with PK, only with indexes , attributes.

I'm need to run code on SAS 9.1 under Windows.

I can use describe table write log to external file and then parse it to get PK columns, but it's not the best solution for sure.

Looks like I can get PK cols from ods indexes table, filter by Unique Index.

Re: Extract PK from Phys. table, load it to new table.

jakarman — Mon, 29 Sep 2014 09:52:43 GMT

why not use the proc datasets with contents out= out2= ? It is a direct way to put that info in a dataset.
It could be there are no constraints. In that case to ODS table is not created (nothing to print). Why doing difficult as it can be done easy.

Re: Extract PK from Phys. table, load it to new table.

Yura2301 — Mon, 29 Sep 2014 10:10:24 GMT

Hi Jaap,

Thanks, it actually what was needed.

I thought contents procedure should also have that output table, but looks like datasets procedure is more useful for that case.

Thanks!