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;
These are great @RichardDeVen - thanks for contributing!
I added a screenshot of a sample report -- just to show how useful this can be!
Thank you @RichardDeVen, very useful.
@ChrisHemedinger, nice, I was just missing something like that this morning 🙂 The cherry of the cake.
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.
Good to begin with Query builder on STP
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.