topic Query metadata repository to get information in SAS Data Management

Query metadata repository to get information

forumsguy — Fri, 24 Feb 2012 19:19:08 GMT

Hi All,

My recent task is to identify all attributes of all tables in Foundation Repository .... I want to display the information of Primary keys, Not null and Indexes particularly... I am able to find this information in one given library for one table but I am confused about getting information about all the tables in repository... Basically, I want the report of all tables with their particular details of my Foundation repository... Any guidance will be really helpful...

Query metadata repository to get information

Cynthia_sas — Fri, 24 Feb 2012 20:43:12 GMT

Hi:

This is not a BASE SAS or Macro question, per se. You might wish to post this question in the Stored Process forum or the Web Report Studio forum, which is where most of the "metadata" people hang out.

cynthia

Query metadata repository to get information

FriedEgg — Fri, 24 Feb 2012 23:09:51 GMT

Just incase your reference to 'metadata' does not actually mean literally querying the metadata server and instead using the SAS/Base dictionary data you collect information about a table, library or group of tables/libraries you could do something along the lines of the following.

proc sql;

create table foobar(label='Some Table?')

( name char(8) not null,

sex char(1),

age num,

height num,

weight num not null,

constraint prim_key primary key(name),

constraint sex check(sex in ('M','F')),

constraint age check(age gt 0),

constraint height check(height gt 0),

constraint weight check(weight gt 0)

);

insert into foobar (name,sex,age,height,weight)

select name,sex,age,height,weight

from sashelp.class;

select a.libname "Library",

a.memname "Table Name",

a.memlabel "Table Label",

a.indxtype "Index Type",

b.name as indxcolname "Index Column Name",

b.indxname "Index Name",

b.nomiss as indxnomiss "Index NOMISS",

b.unique as indxuniq "Index Unique"

from sashelp.vtable a

join sashelp.vindex b on a.libname=b.libname and a.memname=b.memname

where a.libname='WORK' and a.memname='FOOBAR';

select libname "Library",

memname "Table Name",

name "Not Null Column Name"

from sashelp.vcolumn

where libname='WORK' and memname='FOOBAR' and upcase(notnull)='YES';

select a.table_catalog "Libary",

a.table_name "Table Name",

a.constraint_name "Constraint Name",

b.column_name "Constraint Column Name",

c.check_clause "Constraint Check Clause"

from sashelp.vcntabu a

join sashelp.vcncolu b on a.constraint_name=b.constraint_name

left join sashelp.vchkcon c on a.constraint_name=c.constraint_name

where a.table_catalog='WORK' and a.table_name='FOOBAR';

quit;

Library	Table Name	Table Label	Index Type	Index Column Name	Index Name	Index NOMISS	Index Unique
WORK	FOOBAR	Some Table?	SIMPLE	name	name	no	yes

Library	Table Name	Not Null Column Name
WORK	FOOBAR	name
WORK	FOOBAR	weight

Libary	Table Name	Constraint Name	Constraint Column Name	Constraint Check Clause
WORK	FOOBAR	_NM0001_	name
WORK	FOOBAR	_NM0002_	weight
WORK	FOOBAR	age	age	age>0
WORK	FOOBAR	height	height	height>0
WORK	FOOBAR	prim_key	name
WORK	FOOBAR	sex	sex	sex in ('F', 'M')
WORK	FOOBAR	weight	weight	weight>0

Query metadata repository to get information

forumsguy — Sat, 25 Feb 2012 12:31:47 GMT

Wow... thanks a lot Fried Egg.. but what I meant was actually using Metadata datastep functions.... I have used metadata_resolve, getnasl getattr,and getnasn functions to get these attributes.... @Cynthia... how to move this thread to Stored Process or WRS forum ??? Or else should I start another topic there ???

Query metadata repository to get information

art297 — Sat, 25 Feb 2012 16:00:49 GMT

Renee would be the one who could move it but, given that its a weekend, I would just start another topic there.

Re: Query metadata repository to get information

Ddolic — Wed, 18 Apr 2012 12:23:43 GMT

Sorry, I just dump some code fragments:

%macro procmeta(user=DWH,

passwd={sas001}xxx,

in=query, /* Filereferenz auf XML Datei mit Metadaten*/

out=tmpout);

proc metadata

SERVER = "sas-meta.corp.de"

PORT = 8561

REPOSITORY = "KPI"

USERID = "&user"

PASSWORD = "&passwd"

IN = &in

OUT = &out

;

run;

%mend procmeta;

This Macro can query the metadataserver. For IN use some TXT file like this one:

data _null_;

file query;

input;

put _infile_;

datalines;

<Reposid>$METAREPOSITORY</Reposid>

</Objects>

</Templates>

</Options>

</GetMetadataObjects>

;;

run;