topic Re: Finding the Number of Rows and Columns for each dataset in a library in SAS Programming

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

larkjr18 — Fri, 21 Jul 2017 14:58:55 GMT

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.

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

RW9 — Fri, 21 Jul 2017 15:02:29 GMT

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.

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

PaigeMiller — Fri, 21 Jul 2017 15:03:57 GMT

You don't need a macro.

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

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

PaigeMiller — Fri, 21 Jul 2017 15:05:52 GMT

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

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

andreas_lds — Fri, 21 Jul 2017 15:07:27 GMT

Have a look at sashelp.vtable:

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

To late 😉

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

RW9 — Fri, 21 Jul 2017 15:07:19 GMT

Oh yes, NVAR, good spot:

Updated:

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

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

kiranv_ — Fri, 21 Jul 2017 15:30:20 GMT

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;