Count obs in a library

[BrahmanandaRao]

How to count observations for each dataset in a library using macros

[Shmuel]

Before creating a macro you need to know how to do it without a macro.

As for your question - next code will select all datasets in a given library and their number of observations:

data want;
  set sashelp.vtable(where=(libname=<reqired LIBRARY in uppercase>));
        keep memname  nobs;
run;
/** memname=Member Name or table name **/
/** nobs = No Of Observations **/

There are many ways to convert it into macro programing. For example:

%LET lib = MYDATA;
data want;
  set sashelp.vtable(where=(libname="&lib"));
        keep memname  nobs;
run;

OR:

%macro list(lib);
data want;
  set sashelp.vtable(where=(libname="&lib"));
        keep memname  nobs;
run;
%mend list;
%list(MYLIB);

and there are more methods - as using SQL dictionary.

[Reeza]

sashelp.vtable is a view of dictionary.table

[BrahmanandaRao]

Thank you for your solution

[Reeza · Accepted solution]

https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&...

Try the second link for starters. The full code is available for the macro.

[maguiremq]

Do you only want the number of observations? Do you want them labeled in any way? There's a lot of interpretation that we have to do from your question. The below example concatenates all the data sets in the SASHELP library and their respective observations into a macro variable called "observations":

proc sql noprint;
	select
				catx("-", memname, nobs)
					into: observations separated by ", "
	from
				dictionary.tables
	where
				libname = "SASHELP" 	and
				memtype = "DATA";
quit;

%put &observations;

We need more information though. Just number of observations into separate macro variables would be the following:

proc sql noprint;
	select
				nobs
					into: observations1 -
	from
				dictionary.tables
	where
				libname = "SASHELP" 	and
				memtype = "DATA";
quit;

%put _user_;

[BrahmanandaRao]

Thank you so much