Finding the Number of Rows and Columns for each dataset in a library

[larkjr18]

Hey guys,

I'm relatively new to SAS and am trying to write a piece of code in SAS enterprise that finds the number of rows and columns for each data set in a library of 20 or so datasets. I know I need some sort of macro and possibly a nobs= statement but overall I'm generally lost. Any tips or suggestions to point me in the right direction would be much appreciated.

[RW9]

And where do you get the idea you need macro?

data want_obs (keep=memname nobs);
  set sashelp.vtables (where=(libname="<YOURLIB>"));
run;

This will create a dataset with all the obs for each of the datasets in the given library - note replace that text with your libname in upper case.

Same for variables:

data want_cols (keep=memname cnt);
  set sashelp.vcolumn (where=(libname="<YOUR_LIB"));
  by memname;
  retain cnt;
  if first.memname then cnt=1;
  else cnt=cnt+1;
  if last.memname then output;
run;

This keeps a running count of columns per dataset and only outputs 1 row with count for each dataset.

[PaigeMiller]

---

@RW9 wrote:

And where do you get the idea you need macro?

data want_obs (keep=memname nobs);
  set sashelp.vtables (where=(libname="<YOURLIB>"));
run;

This will create a dataset with all the obs for each of the datasets in the given library - note replace that text with your libname in upper case.

Same for variables:

data want_cols (keep=memname cnt);
  set sashelp.vcolumn (where=(libname="<YOUR_LIB"));
  by memname;
  retain cnt;
  if first.memname then cnt=1;
  else cnt=cnt+1;
  if last.memname then output;
run;

This keeps a running count of columns per dataset and only outputs 1 row with count for each dataset.

---

While this will work (except that it's sashelp.vtable and not vtables), the number of variables is in sashelp.vtable as well, no need to add up things in sashelp.vcolumn.

[RW9]

Oh yes, NVAR, good spot:

Updated:

data want_obs (keep=memname nobs nvar);
  set sashelp.vtable (where=(libname="<YOURLIB>"));
run;

[PaigeMiller]

You don't need a macro.

You can simply look in data set (well, actually it's a data view) sashelp.vtable

[andreas_lds]

Have a look at sashelp.vtable:

proc sql;
   select MemName, NVar, nobs
      from sashelp.vtable
         where Libname = 'YOUR_LIB'
   ;
quit;

To late 😉

[kiranv_ · Accepted solution]

All the info you need is sashelp.vtable and you can get by below query. Two things to keep in mind . SASHELP.vtable is dictionary view. Any queries run against dictionary views will be slow or very slow. So always have where clause and also put the libname you want in upper case as shown in the code.

proc sql ;
select libname, memname, nobs as totalrows, nvar as totalcolumns from sashelp.vtable
where libname in ('LIB1','LIB2');
quit;