Examples: DATA Step Functions for Reading Metadata

[#1]

The book  "SAS® 9.4 Language Interfaces to Metadata, Third Edition" has a section 
Examples: DATA Step Functions for Reading Metadata.  While informative, the examples are overly commented and uses some looping constructs that are difficult to follow. 

I rewrote the five examples using a looping construct that follows this clean pattern

do index = 1 by 1 while (metadata_<index-based-function> ( ... args .. , index, <return-variable>, ...) > 0);

  * process nth metadata item available in <return-variable> ;
  ...

end;

Sample output (report on metadata libraries):

Here is the code (that is also in the attached download)

%* Example: Listing Libraries and Their Associated Directory or Database Schema ;

data metadata_libraries;

  length liburi upasnuri $256 name $128 type id $17 libref engine $8 path mdschemaname schema $256;

  keep name libref engine path mdschemaname schema type;

  call missing(liburi,upasnuri,name,engine,libref,type,id,path);

  do libobj_index = 1 by 1 while ( metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",libobj_index,liburi) > 0 );

    call missing (name, engine, libref);

    rc=metadata_getattr(liburi,'Name',name);
    rc=metadata_getattr(liburi,'Engine',engine);
    rc=metadata_getattr(liburi,'Libref',libref);

    do n = 1 by 1 while (metadata_getnasn(liburi,'UsingPackages',n,upasnuri) > 0 and n < 1000);
      rc=metadata_resolve(upasnuri,type,id);

      call missing (path, mdschemaname, schema);

      if type='Directory' then do;
		    rc=metadata_getattr(upasnuri,'DirectoryName',path);
			  output;
      end; 
      else
      if type='DatabaseSchema' then do;
        rc=metadata_getattr(upasnuri,'Name',mdschemaname);
        rc=metadata_getattr(upasnuri,'SchemaName',schema);
        output;
      end; 
    end;
  end;
run;

title "Metadata libraries";
proc print data=metadata_libraries; 
run;
title;



%* Example: Listing Libraries and Their Server Contexts ;

data libraries;
  length LibId LibName $ 32 LibRef LibEngine $ 8 LibPath $ 256 ServerContext uri uri2 type id $ 256 server $ 32;

    label
      LibId = "Library Id"
      LibName = "Library Name"
      LibRef = "SAS Libref"
      LibEngine = "Library Engine"
      ServerContext = "Server Contexts"
      LibPath = "Library Path"
    ;

    call missing (uri,uri2);

    do n = 1 by 1 while (metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri) > 0);

      call missing (of Lib:, ServerContext);

      rc=metadata_getattr(uri,"Id",LibId);
      rc=metadata_getattr(uri,"Name",LibName);
      rc=metadata_getattr(uri,"Libref",LibRef);
      rc=metadata_getattr(uri,"Engine",LibEngine);

      do n2 = 1 by 1 while (metadata_getnasn(uri,"DeployedComponents",n2,uri2) > 0);

        call missing (server);

        rc=metadata_getattr(uri2,"Name",server);

        * create space separated list of servers;
        ServerContext=catx(" ",ServerContext,quote(trim(server)));
      end;

      do n2 = 1 by 1 while (metadata_getnasn(uri,"UsingPackages",n2,uri2) > 0);

        call missing (type,id,LibPath);

        rc=metadata_resolve(uri2,type,id);

        select (type);
          when ('Directory') rc=metadata_getattr(uri2,"DirectoryName",LibPath);
          when ('DatabaseSchema') rc=metadata_getattr(uri2, "Name", LibPath);
          otherwise LibPath="*unknown*";
        end;

        OUTPUT;
      end;
    end;

    keep
      LibId
      LibName
      LibRef
      LibEngine
      ServerContext
      LibPath
    ; 
run;

title "Metadata libraries v.2";
proc print data=work.Libraries label; 
run;
title;



%* Example: Listing Logins and Their Associated Identities and Authentication Domains ;

data logins;

  length
    LoginObjId UserId IdentId AuthDomId $ 17
    IdentType $ 32
    Name DispName Desc uri uri2 uri3 AuthDomName $ 256
  ;

  call missing (IdentType, IdentId, Name, DispName, Desc, AuthDomId, AuthDomName, uri);

  do n = 1 by 1 while (metadata_getnobj("omsobj:Login?@Id contains '.'",n,uri) > 0);

    call missing (LoginObjId, UserId);

    rc=metadata_getattr(uri,"Id",LoginObjId);
    rc=metadata_getattr(uri,"UserId",UserId);

    call missing(uri2);

    do n2 = 1 by 1 while(metadata_getnasn(uri,"AssociatedIdentity",n2,uri2) > 0);

      call missing (IdentType,IdentId, Name, DispName, Desc);

      rc=metadata_resolve(uri2,IdentType,IdentId);

      rc=metadata_getattr(uri2,"Name",Name);
      rc=metadata_getattr(uri2,"DisplayName",DispName);
      rc=metadata_getattr(uri2,"Desc",Desc);

      call missing (uri3);

      do n3 = 1 by 1 while (metadata_getnasn(uri,"Domain",n3,uri3) > 0);

        call missing(AuthDomId,AuthDomName);

        rc=metadata_getattr(uri3,"Id",AuthDomId);
        rc=metadata_getattr(uri3,"Name",AuthDomName);

        output;
      end;
    end;
  end;

  keep LoginObjId UserId IdentType Name DispName Desc AuthDomId AuthDomName; 
run;


title "Metadata logins";
proc print data=logins;
  var LoginObjId UserId IdentType Name DispName Desc AuthDomId AuthDomName;
run;



%* Example: Listing User Group Memberships ;

data users_grps;

  length uri name dispname group groupuri $256 id MDUpdate $20;
  
  call missing(uri);

  do n = 1 by 1 while (metadata_getnobj("omsobj:Person?@Id contains '.'",n,uri) > 0);

    call missing(Name,DispName);

    rc=metadata_getattr(uri, "Name", Name);
    rc=metadata_getattr(uri, "DisplayName", DispName);

    call missing(groupuri);
    n2=metadata_getnasn(uri,"IdentityGroups",1,groupuri);

    if n2 in (-3,-4) then do;
      group="No groups";
      output;
      continue;
    end;

    do n2 = 1 by 1 while (metadata_getnasn(uri,"IdentityGroups",n2,groupuri) > 0);

      call missing(group,MDUpdate);

      rc=metadata_getattr(groupuri, "Name", group);
	    rc=metadata_getattr(groupuri, "MetadataUpdated", MDUpdate);
      * rc=metadata_getattr(groupuri, "Id", id);

    	output;
    end;
  end;

  keep name dispname MDUpdate group id;
run;

proc report data=users_grps missing;
  columns name dispname group MDUpdate;
  define name / order 'User Name' format=$30.;
  define dispname / order 'Display Name' format=$30.;
  define group / order 'Group' format=$30.;
  define MDUpdate / display 'Updated' format=$20.;
  break after name / skip;
run;
/**/ options source;


%* Example: Listing Users and Their Logins ;

data work.Identities;

  length 
    IdentId IdentName DispName ExtLogin IntLogin DomainName $256
    uri uri2 uri3 uri4 $256
    prop value asn $256
  ;

  label
  	IdentId    = "Identity Id"
  	IdentName  = "Identity Name"
  	DispName   = "Display Name"
  	ExtLogin   = "External Login"
  	IntLogin   = "Internal Login (@saspw)"
  	DomainName = "Authentication Domain"
  ;

  call missing(IdentId, IdentName, DispName, ExtLogin, IntLogin, DomainName);
  call missing(uri, uri2, uri3, uri4, prop, value, asn);

  n2=1;

  do n = 1 by 1 while (metadata_getnobj("omsobj:Person?@Id contains '.'",n,uri) > 0);

  	objrc=metadata_getattr(uri,"Id",IdentId);
  	objrc=metadata_getattr(uri,"Name",IdentName); 
  	objrc=metadata_getattr(uri,"DisplayName",DispName);

    IntLogin = "**None**";
    ExtLogin = "**None**";
    DomainName="**None**";

    if n=1 then put 'Person associations (from ' IdentName +(-1) ')' / 'This program examines Logins and InternalLoginInfo';
    if n=1 then
    do n2 = 1 by 1 while (metadata_getnasl(uri, n2, asn) > 0);
      put 'NOTE: ' n2 +(-1) '. ' asn=;
    end;

    do n2 = 1 by 1 while (metadata_getnasn(uri,"InternalLoginInfo",n2,uri2) > 0);
      IntLogin = 'Yes';
    end;

    n2 = n2 - 1;
    if n2 > 1 then put 'ERROR: Um, Metadata says the internal login count is ' n2;

    do n2 = 1 by 1 while (metadata_getnasn(uri,"Logins",n2,uri3) > 0);
      rc=metadata_getattr(uri3,"UserID",ExtLogin);
      DomainName="**None**";
      * obtain first (and typically ? only Domain associated with the login);

      if (metadata_getnasn(uri3,"Domain",1,uri4) > 0) then 
        rc=metadata_getattr(uri4,"Name",DomainName);

      output; * one output row per external login;
    end;

    if n2 = 1 then output; * there were no external logins, so output is needed here;
  end;

  keep IdentId IdentName DispName ExtLogin IntLogin DomainName; 
run;

title "Identity logins";
proc print data=work.Identities label;
run ;
title;

[ChrisHemedinger]

These are great @RichardDeVen - thanks for contributing!

[ChrisHemedinger]

I added a screenshot of a sample report -- just to show how useful this can be!

[JuanS_OCS]

Thank you @RichardDeVen, very useful.

@ChrisHemedinger, nice, I was just missing something like that this morning 🙂 The cherry of the cake.

[SASKiwi]

Very appreciative of the effort to produce this article. Thank you @RichardDeVen! I've always been put off by the effort required to unravel metadata complexities despite realising the undoubted benefits. These examples sure help you get productive with metadata reporting a lot faster. 

[BNYMSubrat]

Good to begin with Query builder on STP