topic Re: Loop through thousands of SAS datasets for metadata in SAS Programming

Loop through thousands of SAS datasets for metadata

Billybob73 — Tue, 30 Jan 2018 22:02:16 GMT

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.

Re: Loop through thousands of SAS datasets for metadata

Reeza — Tue, 30 Jan 2018 22:09:35 GMT

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.

Re: Loop through thousands of SAS datasets for metadata

ballardw — Tue, 30 Jan 2018 23:25:10 GMT

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

Re: Loop through thousands of SAS datasets for metadata

Kurt_Bremser — Wed, 31 Jan 2018 08:13:47 GMT

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.

Re: Loop through thousands of SAS datasets for metadata

Billybob73 — Thu, 01 Feb 2018 09:26:45 GMT

Hi Kurt,

This is the way to do it ! Thanks !!

Rgds