Loop through thousands of SAS datasets for metadata

[Billybob73]

Hi,
I'm in a situation that I've thousands of SAS datasets scattered all over both the LAN and the SAS Server.
These datasets are not necessarily in SAS libraries.
Is there a way to get their metadata in a loop ?
In a huge table I need information per dataset such as variable, type, length, varnum, format
Thanks !
Regards
B.

[Reeza]

You can assign a library, then you can grab the data from SASHELP.VCOLUMN. 

I would probably first check my library locations, scan my LAN for SAS7BDAT files using an OS approach (or see SAS Macro Appendix for a macro that does this). Check if any folders don't match your libraries from (SASHELP.VLIBNAM) and assign those libraries. Once that's done you can pull all your data from SASHELP.VCOLUMN. 

[ballardw]

For added fun are any of these sets in older SAS formats such as SSD or SD2 extensions?

[Kurt_Bremser · Accepted solution]

On UNIX, I'd run

find / -name \*.sas7bdat > /tmp/dataset.list

From the list file, read the physical filenames. Use string functions to extract the pathname and the dataset name.

From dictionary.libnames (SQL) get the physical path names and logical libnames.

Merge those datasets by physical path.

Create a macro that retrieves the information you want (for a single dataset):

%macro get_meta(libname,memname,physical);
%if "&physical" ne ""
%then %do;
  libname &libname "&physical";
%end;

proc contents
  data=&libname..&memname
  out=temp
  noprint
;
run;

proc append data=temp base=target.mymetadata;
run;

%if "&physical" ne ""
%then %do;
  libname &libname clear;
%end;

%mend;

Run that macro off your dataset of datasets with call execute:

data _null_;
set mydatasets;
/* insert a where condition to exclude predefined libraries like WORK, SASUSER, SASHELP etc */
if libname = ""
then call execute('%nrstr(%get_meta)(__TEMP,' !! trim(memname) !! ',' !! trim(physical_path) !! ');');
else call execute('%nrstr(%get_meta)(' !! trim(libname) !! ',' !! trim(memname) !! ');');
run;

Be aware that you need to have the necessary permissions; it's best to run the find as superuser, so you get no error messages for inaccessible directories that are not of interest anyway (like /etc/security, where you will not find any SAS datasets). The same goes for all directories where datasets are found.

[Billybob73]

Hi Kurt,

This is the way to do it ! Thanks !!

Rgds

B