BookmarkSubscribeRSS Feed
sabid
Calcite | Level 5

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?

24 REPLIES 24
Reeza
Super User
SASHELP.VTABLE
+
SASHELP.VCOLUMN

or dictionary.table, dictionary.column

Those tables have all the information you'll need.
sabid
Calcite | Level 5

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! 

Reeza
Super User
Those are table names. Look them up in your system.
sabid
Calcite | Level 5

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

Reeza
Super User
You should go ask whoever asked you to do this. As an admin your really should be familiar with the systems.
sabid
Calcite | Level 5

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

sabid
Calcite | Level 5

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

Reeza
Super User
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.
ErikLund_Jensen
Rhodochrosite | Level 12

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.

 

 

sabid
Calcite | Level 5

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.

ErikLund_Jensen
Rhodochrosite | Level 12

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.

Reeza
Super User
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.
Patrick
Opal | Level 21

@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#n0sk6o15955yoen19n... 

 

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.

 

 

sabid
Calcite | Level 5

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! 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 24 replies
  • 1475 views
  • 3 likes
  • 4 in conversation