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

Reply
Contributor
Posts: 25

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

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.

Super User
Super User
Posts: 7,942

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

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.

Trusted Advisor
Posts: 1,913

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


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.

Super User
Super User
Posts: 7,942

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

Posted in reply to PaigeMiller

Oh yes, NVAR, good spot:

Updated:

data want_obs (keep=memname nobs nvar);
  set sashelp.vtable (where=(libname="<YOURLIB>"));
run;
Trusted Advisor
Posts: 1,913

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

You don't need a macro.

 

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

Super Contributor
Posts: 345

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

[ Edited ]

Have a look at sashelp.vtable:

 

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

 

To late ;-)

PROC Star
Posts: 325

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

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;
Ask a Question
Discussion stats
  • 6 replies
  • 170 views
  • 0 likes
  • 5 in conversation