Generic way to get SAS Table metadata URI

Question

I'm building a utility that leverages the SAS metadata ID (or URI) of a table object. The following code works fine for getting the ID when the library uses the BASE engine:

%let mylib=SOMELIB;
data output (keep=uri dataname);
  length uri $100 dataname $256;
  uri='';
  i=1;
  do until (rc<0);
    rc=metadata_getnasn("omsobj:SASLibrary?@Libref='&mylib'","Tables",i,uri);
    put rc=;
    prc=metadata_getattr(uri,"Name",dataname);
    if rc>=0 then output;
    i+1;
    put i=;
  end;
run;

However for other library engines (eg OLEDB, ODBC, REMOTE) SAS will store the information in different properties (eg under "UsingPackages/[my db]/Tables"). I can write conditional logic for each of the library engines I come across, but wondered if there was an easier / more generic way to get the Table ID?

The same issue occurs in reverse (if I search for the Table I still need the SASLibrary to ensure it is unique).

score 2 · Answer 1 · answered Nov 12 '15 at 16:51

The code in this article was extremely helpful for our SAS admins:

http://support.sas.com/documentation/cdl/en/lrmeta/63180/HTML/default/viewer.htm#p1k9zipe59ha2an1pq34gu143lay.htm

Example: Listing Libraries and Their Server Contexts

This program uses the SAS metadata DATA step functions to return more detailed information about the libraries. The results are returned to a Libraries data set in the Work library. The requested data includes the library metadata ID, the library name, libref, engine, path on the file system (or if DBMS data, the DBMS path), and the server contexts to which the library is associated.

It'll give you everything you want and more.

Thanks.. I'm resigned to the fact that a conditional script like the one you reference will be necessary. Am nearly finished, and will post it here shortly. — Allan Bowe, Nov 12 '15 at 17:55

Allan Bowe · Answer 2 · 2020-08-08T15:15:54.810

I wrote a macro for this today, located here: https://github.com/sasjs/core/blob/main/meta/mm_gettableid.sas

Reproduced below:

/**
  @file mm_gettableid.sas
  @brief Get the metadata id for a particular table
  @details Provide a libref and table name to return the corresponding metadata id
  in an output datataset.

  Usage:

      - get a table id
      %mm_gettableid(libref=METALIB,ds=SOMETABLE,outds=iwant)

  @param libref= The libref to search
  @param ds= The input dataset to check
  @param outds= the dataset to create that contains the `tableuri`
  @param mDebug= set to 1 to show debug messages in the log

  @returns outds  dataset containing `tableuri` and `tablename`

  @version 9.3
  @author Allan Bowe

**/

%macro mm_gettableid(
     libref=
    ,ds=
    ,outds=work.mm_gettableid
    ,mDebug=0
)/*/STORE SOURCE*/;

%local mD;
%if &mDebug=1 %then %let mD=;
%else %let mD=%str(*);
%&mD.put Executing &sysmacroname..sas;
%&mD.put _local_;

data &outds;
  length uri usingpkguri id type tableuri tablename tmpuri $256;
  call missing(of _all_);
  keep tableuri tablename;
  n=1;
  rc=0;
  if metadata_getnobj("omsobj:SASLibrary?@Libref='&libref'",n,uri)<1 then do;
    put "Library &libref not found";
    stop;
  end;
  &mD.putlog "uri is " uri;
  if metadata_getnasn(uri, "UsingPackages", 1, usingpkguri)>0 then do;
    rc=metadata_resolve(usingpkguri,type,id);
    &mD.putlog "Type is " type;
  end;

  if type='DatabaseSchema' then tmpuri=usingpkguri;
  else tmpuri=uri;
  
  t=1;
  do while(metadata_getnasn(tmpuri, "Tables", t, tableuri)>0);
    t+1;
    rc= metadata_getattr(tableuri, "Name", tablename);
    &mD.putlog "Table is " tablename;
    if upcase(tablename)="%upcase(&ds)" then do;
      output;
    end;
  end;
run;

%mend;

Generic way to get SAS Table metadata URI

2 Answers2

Linked