SAS Folder Path for Metadata tables

Question

I'm wondering if anyone knows how to retrieve the sas folder path for metadata tables?

I would like to list the folder path for each tables located on metadata.

Example:

Table FactPortfolio is under the following fodler structure in management console: Commerc-->Sweden-->Portfolios-->Resources-->Tables

In metadata browser in sas, I can find the folder path if I click on tables and trees and parent trees until i reach the top hierarchy. However, I would like to retrieve it with metadata data step functions in SAS.

BR Jonas

score 1 · Answer 1 · answered Mar 18 '14 at 13:23

1

In Base you can use sashelp tables / dictionary views, e.g.

/* SQL Dictionary */
proc sql ;
  create table memlist as
  select *
  from dictionary.tables
  where libname='WORK'
  order by memname ;
quit ;

/* SASHELP */
data memlist ;
  set sashelp.vmember (where=(libname='WORK')) ;
run ;

answered Mar 18 '14 at 13:23

Chris J

7,549
2
25
25

1

Apologies, this gives the datasets within a library, but not the library path. You could try `sashelp.vslib` or `sashelp.vlibnam`. – Chris J Mar 18 '14 at 13:27
I believe the OP was looking for the metadata path of the table object, as opposed to the physical path of the library.. – Allan Bowe Nov 07 '16 at 10:56

Allan Bowe · Answer 2 · 2020-09-20T20:23:23.337

This can be derived once you know the table URI, eg as follows:

%let metauri=OMSOBJ:PhysicalTable\A5HOSDWY.BE0006N9;
/* get metadata paths */
data ;
  length tree_path $500 tree_uri parent_uri parent_name $200;
  call missing(tree_path,tree_uri,parent_uri,parent_name);
  drop tree_uri parent_uri parent_name rc ;
  
  uri="&metauri";
  rc=metadata_getnasn(uri,"Trees",1,tree_uri);
  rc=metadata_getattr(tree_uri,"Name",tree_path);

  do while (metadata_getnasn(tree_uri,"ParentTree",1,parent_uri)>0);
    rc=metadata_getattr(parent_uri,"Name",parent_name);
    tree_path=strip(parent_name)||'/'||strip(tree_path);
    tree_uri=parent_uri;
  end;
  tree_path='/'||strip(tree_path);
run;

Example code for getting table URI's can be found here and here.

EDIT 20200920:

Here is a macro for getting a table uri: https://github.com/sasjs/core/blob/main/meta/mm_gettableid.sas

You can also export all tables, and their metadata paths, using this code:

* load macros;
filename mc url "https://raw.githubusercontent.com/sasjs/core/main/all.sas";
%inc mc;

* invoke macro;
%mm_tree(root=%str(/) ,types=Table ,outds=myoutputdataset)

SAS Folder Path for Metadata tables

2 Answers2

Linked