topic Re: How to get a list of all datasets and their variables in a sas library? in SAS Programming

How to get a list of all datasets and their variables in a sas library?

aasdfafafsdfsaf — Wed, 16 Aug 2023 14:38:27 GMT

Hi,

I have a library of 100+ datasets each with 10+variables. I was wondering if it was possible to output an excel table that had the table name and the column names to the right kind of like the format below.

Any help would be greatly appreciated.

Thank you.

Re: How to get a list of all datasets and their variables in a sas library?

Reeza — Wed, 16 Aug 2023 14:49:52 GMT

Check out sashelp.vcolumn and sashelp.vtable views in the sashelp library.

Re: How to get a list of all datasets and their variables in a sas library?

ballardw — Wed, 16 Aug 2023 15:09:27 GMT

Something like this perhaps? You would specify your library name instead of WORK and the name stored is in uppercase so provide it that way.

proc transpose data=sashelp.vcolumn 
   out= trans  (drop=_name_ _label_)
   prefix=Varname;
   where libname='WORK';
   by memname;
   var name;
run; 

proc print data=trans;
run;

Depending on the purpose of this exercise there are other ways to use the Vcolumn data.

For example this creates a table that checks if the variables of the same name are of the same type

proc tabulate data=sashelp.vcolumn;
   where libname='WORK';
   class memname name type;
   table name*type,
         memname*n=' '
         /misstext=' '
   ;
run;

The columns are the data sets a 1 indicates that the variable name and type on the left of the row is in the data set.

If you have two types for the same variable they would be next to each other in rows to see which is where.

The n =' ' suppresses the column label of N. The Misstex=' ' table option displays no text instead of the default . for all the missing counts (variables not in the given data set column)

Re: How to get a list of all datasets and their variables in a sas library?

Tom — Wed, 16 Aug 2023 16:32:20 GMT

That is what PROC CONTENTS is for.

proc contents data=mylib._all_ noprint out=contents; run;

What do you want to DO with the list? if you would like it to appear in that strange wide format then transpose the data.

proc sort data=contents;
  by libname memname varnum ;
run;

proc transpose data=contents out=wide(drop=_name_ _label_) prefix=var;
  by libname memname ;
  id varnum;
  var name;
run;

Re: How to get a list of all datasets and their variables in a sas library?

DrAbhijeetSafai — Fri, 24 May 2024 05:11:00 GMT

That was helpful @Reeza !

Thank you.

- Dr. Abhijeet Safai

Re: How to get a list of all datasets and their variables in a sas library?

Patrick — Fri, 24 May 2024 10:02:41 GMT

@DrAbhijeetSafai Just adding the bit of code required to store the result in a Excel sheet. Else just using Tom's code so suggest you accept his proposal as solution.

options dlcreatedir;
libname mylib "%sysfunc(pathname(work))\source";
data mylib.class;
  set sashelp.class;
run;
data mylib.air;
  set sashelp.air;
run;

proc contents data=mylib._all_ noprint out=contents; 
run;

proc sort data=contents;
  by libname memname varnum ;
run;

libname exl xlsx "%sysfunc(pathname(work))\myexcle.xlsx";
proc transpose data=contents out=exl.wide(drop=_name_ _label_) prefix=var;
  by libname memname ;
  id varnum;
  var name;
run;