Create Data Inventory

[sabid]

Just to add, there are many libraries for different departments and such, I am looking for a list of datasets that captures it every datasets in all libraries? I can see these in the management console.

Thanks!

[Patrick]

---

@sabid wrote:

Just to add, there are many libraries for different departments and such, I am looking for a list of datasets that captures it every datasets in all libraries? I can see these in the management console.

Thanks!

---

That's now a slightly different request as compared to your initial question.

- You can have SAS tables (.sas7bdat files) on the file system for which there isn't a library definition in SAS Metadata. Any SAS user can define a libname statement directly in code and point it to any folder on the file system to which the user has read or read/write access.

- If you've got SAS Access modules licensed then SAS metadata library definitions can also point to databases (not only the file system).

- To get all the files (.sas7bdat) on the file system you best search for such files via DIR command.

- To get all the tables for which there is a SAS metadata library definition some code is required which first queries metadata and executes the libname statements as per metadata definition (as this hasn't happened yet for any not pre-assigned library). After that  one can query the SAS dictionary tables (dictionary.tables) to retrieve the SAS tables under these libraries. And if it should only be SAS libraries then one would need to restrict the process to metadata definitions which use a SAS engine and not some database specific engine.

....so: What do you need?

[sabid]

Hmmmm. I would like all table/datasets with library definitions. So the last option seems what I am looking for.

[ErikLund_Jensen]

Hi @sabid 

Here is some code to start with. The first program gives a list of all SAS libraries (with engine = BASE) defined in SAS Metadata with all tables registered in metadata. If you use concatenated directories in a libname (hopefully not) you will get table dublets, because SAS doesn't know which tables are in which library.

The second program gives a list of all physical tables in a directory structure. It should be easy enough to join the two tables, so you can check for directories not defines as libnames, registered tables without a physical existence and unregistered tables.

* Set meta options;
options 
	metaserver="your metadata server"
	metaport=8561
	metaprotocol="BRIDGE"
	metauser="sasbatch"
	metapass="your-password"
	metarepository="Foundation";

* Get list of SAS libraries (engine=BASE);
data lib1(drop=i found rc1 rc2);
	length LibUri $255 LibName $60;
	LibUri = ''; 
	LibName='';
	found = metadata_getnobj("omsobj:SASLibrary?@Engine='BASE'",1,LibUri);
	do i = 1 to found;
		rc1 = metadata_getnobj("omsobj:SASLibrary?@Engine='BASE'",i,LibUri);
		rc2 = metadata_getattr(LibUri,"Name",LibName);
		output;
	end;
run;

* Add directory information to libraries;
data lib2(drop=i found rc1 rc2); 
	set lib1;
	length PackageUri $255 Directory $255;
	found=metadata_getnasn(LibUri,"UsingPackages",1,PackageUri);
	do i = 1 to found;
		PackageUri = '';
		Directory = '';
		rc1 = metadata_getnasn(LibUri,"UsingPackages",1,PackageUri);
		rc2 = metadata_getattr(PackageUri,"DirectoryName",Directory);
		output;
	end;
run;

* Add table information to libraries;
data metadatatables (drop=i found rc1 rc2 rc3 LibUri PackageUri TableUri); 
	set lib2;
	length TableUri TableName FileName $255;
	found=metadata_getnasn(LibUri,"Tables",1,TableUri);
	do i = 1 to found;
		TableUri = '';
		TableName = '';
		FileName = '';
		rc1 = metadata_getnasn(LibUri,"Tables",i,TableUri);
		rc2 = metadata_getattr(TableUri,"Name",TableName);
		rc3 = metadata_getattr(TableUri,"SASTableName",FileName);
		FileName = trim(FileName)||'.sas7bdat';
		output;
	end;
	if found < 1 then output; * include empty libraries;
run;

* Initiate physicaltable;
data physicaltables;
	length Directory FileName $128;
	stop;
run;

* macro to get all files in a folder structure;
%macro getdirdata(startfolder);
	filename dirlist pipe "dir ""&startfolder"" /s /-c /a:-d /o:n /t:w";
	data dirlist (keep=Directory FileName);  	
		length Directory FileName $128;
		retain Directory;
		infile dirlist truncover;
		input rec $char300.;

		if left(rec) = :'Directory of' then Directory = substr(rec,14);
		else if substr(rec,1,1) ne '' then do;
			FileName = substr(rec,37); 
			if scan(FileName,-1,'.') = 'sas7bdat' then output;
		end;
	run;
	filename dirlist clear;

	proc append base=physicaltables data=dirlist;
	run;
%mend;

* Get list of SAS data sets in folder;
* Add macro calls for every top-level folder;
%getdirdata(X:\DWHadmin);
*%getdirdata(X:\Userdata);

[Patrick]

---

@sabid wrote:

Hmmmm. I would like all table/datasets with library definitions. So the last option seems what I am looking for.

---

Building on the code @ErikLund_Jensen posted here a way how you can assign all libraries defined in SAS metadata to then query the SAS dictionary tables to retrieve all physical tables accessible via these libraries. The info gets collected in table work.all_tables

* Get list of SAS libraries (engine=BASE);
data lib1(drop=_:);
	length LibUri $255 Libref $8;
	_nLibs = metadata_getnobj("omsobj:SASLibrary?@Engine='BASE'",1,LibUri);
	do _i = 1 to _nLibs;
		_rc = metadata_getnobj("omsobj:SASLibrary?@Engine='BASE'",_i,LibUri);
		_rc = metadata_getattr(LibUri,"Libref",Libref);
		output;
	end;
  stop;
run;

proc datasets lib=work nolist nowarn;
  delete all_tables;
  run;
quit;

filename codegen temp;
data _null_;
  file codegen;
  set lib1;
  put
    'libname ' Libref 'meta liburi="SASLibrary?@Libref=' "'" Libref +(-1)"'" '" metaout=data;' /
    'proc sql;' /
    '  create table work.tbl_list as' /
    '  select *' /
    '  from dictionary.tables' /
    '  where libname= %upcase("' libref +(-1)'") and memtype="DATA" and libname not in ("WORK","SASHELP")' /
    '  ;' /
    'quit;' /
    'proc append base=work.all_tables data=tbl_list;' /
    'run;quit;' /
    ;
run;

data _null_;
  file codegen mod;
  stop;
run;
%include codegen / source2;

filename codegen clear;

[sabid]

This is perfect! Now I have to get the time to run the code!... I'll try these out and let you guys know what happens.

Thanks so much! 

[sabid]

So guys.. thanks for your help. The solution that worked was a mix of what you guys sent me and a lot of other stuff. 

Define Metadata Server connection. */
options
metaserver="meta.demo.sas.com"
metaport=8561
metauser="sasadm@saspw"
metapass="password"
metarepository=Foundation
metaprotocol=bridge;

data work.libinfo;

/*declare and initialize variables */
length
type $ 20
lib_ref $ 8
lib_uri lib_name app_uri app_name dir_uri tab_uri tab_name $ 50
id lib_id $ 17
path $ 255;
keep lib_ref lib_name tab_name;
call missing(type,id,lib_uri,lib_name,app_uri,app_name,dir_uri,path);

obj="omsobj:SASLibrary?@Id contains '.'"; /* Define library search parameters. */

/* Search Metadata for libraries */

libcount=metadata_resolve(obj,type,id);
put "INFO: Found " libcount "libraries.";
if libcount > 0 then do n=1 to libcount; /* for each library found, extract name and associated properties (first associated application server, path) */

rc=metadata_getnobj(obj,n,lib_uri);
rc=metadata_getattr(lib_uri,"Name",lib_name);
rc=metadata_getattr(lib_uri,"Id",lib_id);
rc=metadata_getattr(lib_uri,"Libref",lib_ref);
rc=metadata_getnasn(lib_uri,"DeployedComponents",1,app_uri);
rc=metadata_getattr(app_uri,"Name",app_name);
rc=metadata_getnasn(lib_uri,"UsingPackages",1,dir_uri);
rc=metadata_getattr(dir_uri,"DirectoryName",path);

/* Define a query to search for any tables associated with the library in Metadata. */

tabobj="omsobj:PhysicalTable?PhysicalTable[TablePackage/SASLibrary[@Id='"||lib_id||"']]";

/* Count how many associations exist. */

tabcount=metadata_resolve(tabobj,type,id);

/* If there are any, pull the name of each one and write out the data set. */

if tabcount > 0 then do t=1 to tabcount;
rc=metadata_getnobj(tabobj,t,tab_uri);
rc=metadata_getattr(tab_uri,"Name",tab_name);
output; /* Push results to table */
end;
call missing (path); /* clear path variable. */
end;
else put "INFO: No libraries to resolve.";
run;

[Patrick]

Just be very clear that with the code you've posted here only tables registered in SAS Metadata will get captured.

[ErikLund_Jensen]

Hi @sabid 

As a follow-up on @Patrick 's latest post: Don't forget the physical tables!

When working on jobs, SAS DI developers often get a library filled with dead tables, that are no longer used in their jobs, but still exists in the directory.

If an obsolete table is deleted from metadata, the physical table is not deleted. The same happens when a table's name is changed and the developer follows the good practice to keep physical name in correspondance with the metadata name. In next run the table is created under the new name, and the old table still exists as a dead table.

There is no easy way for the developers to keep the physical library tidy, so in your Admin role, it will certainly be your responsibility to delete unregistered physical tables from directories and also delete whole unused directories, and this is best done by a scheduled task that keeps the physical directories in sync with the metadata libraries. Just make sure you code an emergency stop, so you don't delete everything if the metadata extract comes out empty.

[sabid]

Great ideas @ErikLund_Jensen and  @Patrick. This is just a starting point. We don't exactly have all aspects of data management and governance figured out. Just capturing what exists is the first step. I know for sure there is lots of clean up required. A code on the line of what you are referring to seems just what I would need. Hopefully I'll get to that as well! Stay tuned as I might be picking your brains for such! 🙂

Thanks again!