Programmatically working with SAS 9 Metadata Libraries as an Administrator
SAS metadata is an integral part of the design of the SAS Intelligence Platform (SAS 9.4). It functions as the “brain” of the platform, with metadata objects referencing users, SAS libraries, tables, reports, servers and more. Here we'll focus on keeping those SAS libraries pointed at the right data sources, and storing information about the tables located at those libraries. Using SAS-provided tools like SAS Management Console or SAS Data Integration Studio, you register tables in metadata to let the platform know about the data that is available at a library. Once registered, SAS 9 assumes and expects those registered tables to be there.
While working with a customer, an interesting question came forth: How can I best manage and update these metadata library references to tables at scale? What if the underlying tables move locations?
PROC METALIB: Refreshing Metadata on one known SAS Library
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
In addition to graphical user interfaces, a programmatic way for maintaining table metadata exists with PROC METALIB. Fun fact: SAS Management Console uses PROC METALIB code behind the graphical user interface to perform its table management operations.
But writing your own PROC METALIB code allows you to include options for maintaining table metadata that are not available in SAS Management Console, such as operating on a subset of tables (via the SELECT statement), or producing a report before making any changes to the table metadata (via the NOEXEC and REPORT statements.)
Here’s an example of PROC METALIB code to report on the changes that would be made with the above statements.
%let whichlib=Orion Star SAS Users;
%let whichrepo=Foundation;
proc metalib;
omr (library="&whichlib" repname="&whichrepo");
noexec;
report;
run;
See SAS Help Center: Updating Your Table Metadata to Match Data in Your Physical Tables for more information and sample code for working on a single library with PROC METALIB.
But what about multiple known SAS libraries?
Back to the customer question above: What if I want to assess multiple libraries? Thanks to some macro facility coding, we can wrap the PROC METALIB in a macro program and pass multiple library names to the query. This works best for a list of known libraries, and you want to spend the time writing the name of each into a delimited list in a SAS program.
%macro do_the_metalib;
%put NOTE: Running METALIB on &whichlib;
proc metalib;
omr (library="&whichlib" repname="&whichrepo");
noexec;
report;
run;
%mend;
%macro check_pipeseparated_libs(liblist);
%local i whichlib;
%let i=1;
%let whichlib=%scan(&liblist, &i, |);
%do %while(%length(&whichlib));
%do_the_metalib;
%let i=%eval(&i + 1);
%let whichlib=%scan(&liblist, &i, |);
%end;
%mend;
%let whichrepo=Foundation;
%check_pipeseparated_libs(Orion Star SAS Users|Chocolate Enterprises Library|Sales History Library);
Some links and references used here are listed below. But essentially the macro program scans the list of library names you’ve passed it until it reaches the end of your pipe separated list, and performs the PROC METALIB code block on each one. I’ve set the do_the_metalib program separate to make it easier for you to find and modify as needed.
- SAS Help Center: Defining and Calling Macros
- SAS Help Center: %DO %WHILE Macro Statement
- SAS Help Center: %LENGTH Macro Function
DATA Step Functions for Reading Metadata: What if I have hundreds of Libraries?
Time to upgrade the tools we're using. The DATA Step Functions for Reading Metadata are going to be our friend with this one. The majority of the initial code comes from SAS Documentation: SAS Help Center: Examples: DATA Step Functions for Reading Metadata. It retrieves a series of related metadata attributes when we look up a SAS library. I ended up reusing this code so often during the writing of this article that I wrapped it into a Macro program for easy reuse. Note, I removed or modified many of the code comments for brevity but go and check the link above for the original code.
%macro showmethelibraries;
data work.Libraries;
length LibId LibName $ 256 LibRef LibEngine $ 8 LibPath $ 256 ServerContext uri uri2 type $ 256 server $ 32;
label
LibId = "Library Id"
LibName = "Library Name"
LibRef = "SAS Libref"
LibEngine = "Library Engine"
ServerContext = "Server Contexts"
LibPath = "Library Path"
;
call missing(LibId,LibName,LibRef,LibEngine,LibPath,ServerContext,uri,uri2,type,server);
n=1;
n2=1;
/* Here's where we actually look for Libraries. */
rc=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri);
if rc<=0 then
put "NOTE: rc=" rc
"There are no Libraries defined in this repository"
" or there was an error reading the repository.";
/* If it found a library, retrieve some attributes */
do while(rc>0);
objrc=metadata_getattr(uri,"Id",LibId);
objrc=metadata_getattr(uri,"Name",LibName);
objrc=metadata_getattr(uri,"Libref",LibRef);
objrc=metadata_getattr(uri,"Engine",LibEngine);
/* Not all the attributes are stored in the Library Metadata object! *\
/* Some things, like Server Context, Path, or database schema are stored in associated metadata objects! */
/* Code down here looks through those associations */
objrc=metadata_getnasn(uri,"DeployedComponents",n2,uri2);
if objrc<=0 then
do;
put "NOTE: There is no DeployedComponents association for "
LibName +(-1)", and therefore no server context.";
ServerContext="";
end;
do while(objrc>0);
objrc=metadata_getattr(uri2,"Name",server);
if n2=1 then
ServerContext=quote(trim(server));
else ServerContext=trim(ServerContext)||" "||quote(trim(server));
n2+1;
objrc=metadata_getnasn(uri,"DeployedComponents",n2,uri2);
end; /*do while objrc*/
n2=1;
objrc=metadata_getnasn(uri,"UsingPackages",n2,uri2);
if objrc<=0 then
do;
put "NOTE: There is no UsingPackages association for "
LibName +(-1)", and therefore no Path.";
LibPath="";
end;
do while(objrc>0);
objrc=metadata_resolve(uri2,type,id);
if type='Directory' then
objrc=metadata_getattr(uri2,"DirectoryName",LibPath);
else if type='DatabaseSchema' then
objrc=metadata_getattr(uri2, "Name", LibPath);
else LibPath="*unknown*";
output;
LibPath="";
n2+1;
objrc=metadata_getnasn(uri,"UsingPackages",n2,uri2);
end; /*do while objrc*/
ServerContext="";
n+1;
/* Look for other libraries */
n2=1;
rc=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri);
end; /*do while rc*/
/* Keep these variables in the output dataset. */
keep LibId LibName LibRef LibEngine ServerContext LibPath;
run;
%mend;
%showmethelibraries;The code creates a table named LIBRARIES that lists information about the libraries in your environment. You might find that information useful, so I left that in and used a subset of the library information stored in METALIB_LIST. Here we keep only the UID and LIBNAME of the libraries, then run PROC METALIB against the libraries. Add this to the bottom of the program:
data work.metalib_list;
set work.libraries;
where not missing(LibName)
/* optional filter to only get BASE Libraries. Change this if you want others. */
and LibEngine in ('BASE','V9');
keep LibName LibId;
run;
%let whichrepo=Foundation;
/* Remember down here to remove NOEXEC / REPORT / add other parameters once you're satisfied with the test output.*/
data _null_;
set work.metalib_list;
length cmd $500;
cmd = catt(
'proc metalib;',
' omr (library="', trim(LibName),
'" repname="', "&whichrepo", '");',
' noexec;',
' report;',
'run;'
);
putlog 'NOTE: Running METALIB for ' LibName;
call execute(cmd);
run;
As I wrote in the code comments, remember to remove NOEXEC or REPORT once you’re satisfied with the test report output, and add statements such as update_rule=(delete); in order to perform the table synchronization with the data source.
Beyond Reading Metadata: How to programmatically modify Metadata for multiple SAS libraries?
Up until this point, we’ve been doing a relatively easy task: reading metadata. This last step requires some extra finesse. The original question as a reminder so you do not have to scroll back up was: “How can I best manage and update these metadata references to tables if they move?”
Let’s do just that with the DATA Step Functions for Reading and Writing Metadata. Let’s pretend that our data tables needed to move to a new location. What once was stored at /opt/sas/Workshop/OrionStar now lives at /opt/sas/Workshop/NEW_NFS_SHARE/OrionStar.
Important: Take a metadata server backup before you make any changes from this point on. Up until now we’ve just been reading things from metadata. While this worked in my environment, you should review any code and proceed with caution before running anything like this in a production environment without testing it first.
%showmethelibraries; /*this only runs if you initialized the macro program above. Copy and paste as needed in your code.*/
data things_2_update;
set libraries;
where LibPath like '/opt/sas/Workshop/OrionStar%';
keep LibId LibName LibPath NewLibPath;
run;
data _null_;
set things_2_update;
length uri uri2 type id $256;
length OldPath NewPath $512;
retain INPATH OUTPATH;
/* EDIT THESE */
INPATH = '/opt/sas/Workshop/OrionStar';
OUTPATH = '/opt/sas/Workshop/NEW_NFS_SHARE/OrionStar';
n = 1;
/* Resolve the Library object by Id */
rc = metadata_getnobj(
cats("omsobj:SASLibrary?@Id='", LibId, "'"),
1,
uri
);
if rc <= 0 then do;
put "ERROR: Library not found:" LibId=;
return;
end;
/* Walk UsingPackages */
rc = metadata_getnasn(uri,'UsingPackages',n,uri2);
do while (rc > 0);
/* Resolve association target */
rc2 = metadata_resolve(uri2,type,id);
/* Physical path lives on Directory objects */
if type = 'Directory' then do;
rc3 = metadata_getattr(uri2,'DirectoryName',OldPath);
if index(OldPath, INPATH) = 1 then do;
NewPath = tranwrd(OldPath, INPATH, OUTPATH);
rc4 = metadata_setattr(uri2,'DirectoryName',NewPath);
if rc4 = 0 then
put 'UPDATED:' LibId= OldPath= NewPath=;
else
put 'ERROR updating:' LibId= rc4=;
end;
end;
n + 1;
rc = metadata_getnasn(uri,'UsingPackages',n,uri2);
end;
run;
%showmethelibraries;
Looking at the results, we have updated the metadata paths for our existing libraries, and they point to a new location where to find their tables.
And finally for good measure, this code will synchronize metadata at the new location: deleting tables not there, adding any new ones, registering them, and producing a report of the output information.
data work.metalib_list;
set work.libraries;
where not missing(LibName)
/* optional filter to only get BASE Libraries. Change this if you want others. */
and LibEngine in ('BASE','V9');
keep LibName LibId;
run;
%let whichrepo=Foundation;
data _null_;
set work.metalib_list;
length cmd $500;
cmd = catt(
'proc metalib;',
' omr (library="', trim(LibName),
'" repname="', "&whichrepo", '");',
' update_rule=(delete);',
' report;',
'run;'
);
putlog 'NOTE: Running METALIB for ' LibName;
call execute(cmd);
run;
Your output might look slightly different if your library already knew about those tables at the source or not.
I hope you find some of this useful and thank you for reading. Please remember that code found here is for informational and for proof-of-concept purposes. Don’t run code untested from strangers. You will need to modify some strings to run this in your own environment. And don’t forget to take your metadata backups before trying anything like this!
SAS Help Center: Examples: DATA Step Functions for Reading Metadata
Find more articles from SAS Global Enablement and Learning here.
