topic Re: Count obs in a library in SAS Programming

Count obs in a library

BrahmanandaRao — Wed, 17 Mar 2021 16:02:48 GMT

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

Re: Count obs in a library

Shmuel — Wed, 17 Mar 2021 16:30:53 GMT

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.

Re: Count obs in a library

Reeza — Wed, 17 Mar 2021 16:31:28 GMT

https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&q=observations%20in%20a%20library

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

Re: Count obs in a library

Reeza — Wed, 17 Mar 2021 16:33:01 GMT

sashelp.vtable is a view of dictionary.table

Re: Count obs in a library

maguiremq — Wed, 17 Mar 2021 16:34:26 GMT

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_;

Re: Count obs in a library

BrahmanandaRao — Wed, 17 Mar 2021 16:41:26 GMT

Thank you for your solution

Re: Count obs in a library

BrahmanandaRao — Wed, 17 Mar 2021 16:42:40 GMT

Thank you so much