topic Re: Create Data Inventory in SAS Data Management

Create Data Inventory

sabid — Thu, 27 Jun 2019 18:25:43 GMT

I want to create a list of all datasets (data inventory) that exist in our SAS server. I am in the SAS Admin role, and have access to management console, DIS and sas eg. Can I write a program to create this list?

Re: Create Data Inventory

Reeza — Thu, 27 Jun 2019 18:40:13 GMT

SASHELP.VTABLE
+
SASHELP.VCOLUMN

or dictionary.table, dictionary.column

Those tables have all the information you'll need.

Re: Create Data Inventory

sabid — Thu, 27 Jun 2019 18:44:46 GMT

Ok.. so i dont think I follow.. what am I supposed to do with this? Can you send more explicit directions? I am new in this role!

Re: Create Data Inventory

Reeza — Thu, 27 Jun 2019 18:46:15 GMT

Those are table names. Look them up in your system.

Re: Create Data Inventory

sabid — Thu, 27 Jun 2019 18:47:53 GMT

Hmmm I'll search this. will it allow me to export as well?

Re: Create Data Inventory

sabid — Thu, 27 Jun 2019 18:49:42 GMT

And by system, do you mean the server environment? or a specific application?

Re: Create Data Inventory

Reeza — Thu, 27 Jun 2019 18:49:58 GMT

Sure, right click the data set and export. This functionality is common across most RDBMS, not just SAS. Each will create it's own metadata tables about the table stored.

Re: Create Data Inventory

Reeza — Thu, 27 Jun 2019 18:50:55 GMT

You should go ask whoever asked you to do this. As an admin your really should be familiar with the systems.

Re: Create Data Inventory

sabid — Thu, 27 Jun 2019 18:56:56 GMT

yeah.. I'm one of those folks who are filling in, so learning the ropes. But thanks for your help.

Re: Create Data Inventory

ErikLund_Jensen — Fri, 28 Jun 2019 16:00:57 GMT

Hi @sabid

SAS is not a database. A SAS data set is just a disk file with the extension sas7bdat, that exists in a given directory, but it is invisible to SAS until the directory is assigned as a SAS Library to the current SAS session with a libname statement.

Then SAS can see the given dictionary as a SAS Library and the files in the directory as SAS tables and supply dictionary information in the SASHELP library. But this works only if the given directory is assigned as a Libname in the current session.

To find all physical SAS data sets in the server, it is necessary to identify all files with extension sas7bdat by a recursive directory listing of the whole server directory structure, or the parts of it that might contain SAS data sets. That cannot be done in SAS, but requires a system command, the DIR command in Windows or the LS command in Linux, and the command must be executed by a user who is authorized to list content in all relevant directories (or read files, if you proceed to step 2 below).

The DIR or LS command can be called from SAS EG or DI, if the command is called in a filename PIPE stetement, and the command output can be read with a SAS data step for further processing. This way it is possible to obtain a list of all directories containing SAS data sets + the data sets in these directories.

In a step 2, a SAS macro can be built to loop over a distinct list of directories and assign them as libnames, and then the full directory information can be obtained from SASHELP tables.

Re: Create Data Inventory

sabid — Fri, 28 Jun 2019 18:03:49 GMT

Hey Erik,

This is sooo much more helpful! I was suggested the sas7bdat, but felt this may be messy. Looks like that has to be part of the solution. I'll have to get the Windows/Linux admins to help me out here.

Thanks. Will keep you posted on how this goes.

Re: Create Data Inventory

ErikLund_Jensen — Fri, 28 Jun 2019 18:17:03 GMT

Hi @sabid

Good luck!. If you post information about your server operating system (WIN or Linux), I will try to find time over the week-end to post some example code.

Re: Create Data Inventory

Reeza — Fri, 28 Jun 2019 19:11:37 GMT

Usually a SAS server with DI and a full set up has a metadata server and a set of registered tables. Are you interested in users every possible table, or the automated data warehouse tables? Those are two different tasks.

Re: Create Data Inventory

Patrick — Sat, 29 Jun 2019 12:47:06 GMT

@sabid

Given that SAS tables are stored as files on a file system if you really don't want anything else than a listing of these objects on file level then easiest is to just use a DIR or LS command to list all files with an extension of SAS7BDAT

Should you also have to deal with native SAS tables which use are in the SPDE or SPDS format then things become a bit harder as with these engines SAS stores a single logical table in multiple physical files.

http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#n0sk6o15955yoen19n9ghdziqw1u.htm

If you also want to list SAS table attributes like column names per table then you need to access the physical tables via a SAS libname statement.

It would help if you could be a bit more specific what you have (like the OS) and what you need (just file names or also table attributes).

Also: Please let us know if you can execute SAS code with option XCMD set.

Re: Create Data Inventory

sabid — Mon, 01 Jul 2019 13:24:08 GMT

Wow.. you guys are great.

So to fill in some details, we have Windows OS (64 bit), and I am wanting to simply get a list of all file names i.e., datasets, in the SAS environment (our local server). Does that help? I did run the proc contents command, and got a number of datasets. I would like a list of these.

Thanks so much Patrick and Erik for the details!

Re: Create Data Inventory

sabid — Mon, 01 Jul 2019 13:36:01 GMT

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!

Re: Create Data Inventory

Patrick — Mon, 01 Jul 2019 15:23:24 GMT

@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?

Re: Create Data Inventory

sabid — Mon, 01 Jul 2019 15:34:07 GMT

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

Re: Create Data Inventory

ErikLund_Jensen — Mon, 01 Jul 2019 18:42:04 GMT

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);

Re: Create Data Inventory

Patrick — Tue, 02 Jul 2019 12:57:18 GMT

@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;