Bring PhysicalTable and associated Tree name into one Dataset

Ddolic — Fri, 12 Mar 2010 09:07:18 GMT

Dear Community.

I try to query a Metadatarepository and bring the result in a dataset so that there will be three columns: The SAS-Name of all Tables, the corresponding ID and the Tree Name where the table resides. I am as far as knowing how to query the information. I try to put them into a template, but that seem to be the tricky part.

Working so far:
data _null_;
file query;
input;
put _infile_;
datalines;

$METAREPOSITORY
PhysicalTable

SAS
261

;;
run;

%procmeta(out=rawdata)

%procmeta only does the PROC METADATA and has all the credential and repository stuff. the out is only for control reasons there.

Now I try to put the results from the Stuff into a dataset. Here we go:

data _null_;
file map;
input;
put _infile_;
datalines;

/GetMetadataObjects/Objects/PhysicalTable

/GetMetadataObjects/Objects/PhysicalTable/Trees/@Name
character
string
17

/GetMetadataObjects/Objects/PhysicalTable/@Id
character
string
17

/GetMetadataObjects/Objects/PhysicalTable/@Name
character
string
40

/GetMetadataObjects

/GetMetadataObjects/Reposid
character
string
17

;
run;

data tmp;
set myxml.Tableinfo ;
run;

I tried several variations of the column definitions and even of the Stuff. either I get the Information on all trees or I get the information on all tables. But I need both in one table. As there are perfectly existing in the output from GetMetadata there has to be a way to get them into the dataset.
Any hints, where I'm wrong?

Thanks

Dubro

Re: Bring PhysicalTable and associated Tree name into one Dataset

DanielSantos — Tue, 16 Mar 2010 11:54:16 GMT

Hi,
you should query both objects (PhysicalTable and Tree) and match them by either TreeID or TableID.

Here's an example, that will query all tables (and their tree associations) and trees and match the two.
[pre]
%let REPID=XXXXXXXXXXXX; * REPLACE WITH THE DESIRED REP ID!!!!;

filename request temp lrecl=2048;

* maps;
filename x_tabs_m temp lrecl=2048;
data _null_;
file x_tabs_m encoding="UTF-8";
put '';
put '';
put '';
put '/GetMetadataObjects/Objects/PhysicalTable';
put 'characterSTRING50/GetMetadataObjects/Objects/PhysicalTable@Id';
put 'characterSTRING200/GetMetadataObjects/Objects/PhysicalTable@Name';
put 'characterSTRING50/GetMetadataObjects/Objects/PhysicalTable/Trees/Tree@Id';
put '

';
put '';
run;
filename x_trees_m temp lrecl=2048;
data _null_;
file x_trees_m encoding="UTF-8";
put '';
put '';
put '';
put '/GetMetadataObjects/Objects/Tree';
put 'characterSTRING50/GetMetadataObjects/Objects/Tree@Id';
put 'characterSTRING200/GetMetadataObjects/Objects/Tree@Name';
put '

';
put '';
run;

* queries;
filename x_tabs temp lrecl=800000;
data _null_;
file request;
put '';
put "&REPID";
put 'PhysicalTable';
put '';
put 'SAS';
put '276';
put '';
put '';
put '';
put '';
put '';
put '';
run;
proc metadata in=request out=x_tabs;
run;

filename x_trees temp lrecl=800000;
data _null_;
file request;
put '';
put "&REPID";
put 'Tree';
put '';
put 'SAS';
put '276';
put '';
put '';
put '';
put '';
run;
proc metadata in=request out=x_trees;
run;

* extract desired metadata;
libname x_tabs xml xmlmap=x_tabs_m;
libname x_trees xml xmlmap=x_trees_m;
proc sql;
select T1.TabName, T1.TabId, T2.TreeName, T2.TreeId
from x_tabs.x_tabs as T1 left outer join x_trees.x_trees as T2
on T1.TreeID = T2.TreeId;
run;
[/pre]
Cheers from Portugal.

Daniel Santos @ www.cgd.pt

topic Re: Bring PhysicalTable and associated Tree name into one Dataset in SAS Programming

Bring PhysicalTable and associated Tree name into one Dataset

Re: Bring PhysicalTable and associated Tree name into one Dataset