topic Re: How to get the count of total records from all the tables in a library. in SAS Programming

How to get the count of total records from all the tables in a library.

Srigyan — Tue, 03 Apr 2018 10:42:43 GMT

How to get the count of total records from all the tables in a library. I have one library with multiple datasets. I want to know what is the number of records in each of the tables. how can I do it?

Re: How to get the count of total records from all the tables in a library.

Kurt_Bremser — Tue, 03 Apr 2018 10:51:01 GMT

Retrieve the nobs from dictionary.tables or sashelp.vtable.

Re: How to get the count of total records from all the tables in a library.

Srigyan — Tue, 03 Apr 2018 10:57:35 GMT

noobs is blank. is there any macro which can help.

Re: How to get the count of total records from all the tables in a library.

RW9 — Tue, 03 Apr 2018 10:58:55 GMT

proc sql;
  select sum(nobs) from sashelp.vtable where libname="YOURLIB";
quit;

Replace yourlib with your libname, and make sure its upper case.

Re: How to get the count of total records from all the tables in a library.

Kurt_Bremser — Tue, 03 Apr 2018 11:15:20 GMT

@Srigyan wrote:

noobs is blank. is there any macro which can help.

"noobs" is an option for proc print. Read my post thoroughly.

Here is some code for reference:

data class;
set sashelp.class;
run;

data cars;
set sashelp.cars;
run;

proc sql;
select sum(nobs) as nobs from dictionary.tables where libname = 'WORK' and memtype = 'DATA';
select memname, nobs from dictionary.tables where libname = 'WORK';
quit;

Result:

                        nobs
                    --------
                         455
                                                

                                      Number of
                                       Physical
 Member Name                       Observations
 ----------------------------------------------
 CARS                                       428
 CLASS                                       19
 _PRODSAVAIL                                  8

Re: How to get the count of total records from all the tables in a library.

Srigyan — Tue, 03 Apr 2018 12:42:05 GMT

some i am getting dots(.) in nobs, i am not getting any values.

Re: How to get the count of total records from all the tables in a library.

Kurt_Bremser — Tue, 03 Apr 2018 13:04:21 GMT

Don't feed me meaningless oneliners, POST YOUR CODE. And run mine AS IT IS, so you get a reference.

Re: How to get the count of total records from all the tables in a library.

Srigyan — Tue, 03 Apr 2018 13:43:57 GMT

proc sql;
select sum(nobs) as nobs from dictionary.tables where libname = 'G2NONPII' and memtype = 'DATA';
select memname, nobs from dictionary.tables where libname = 'G2NONPII';
quit;

its giving me

TABLE NAME	Nobs
T1	.
T2	.
T3	.

Re: How to get the count of total records from all the tables in a library.

ChrisHemedinger — Tue, 03 Apr 2018 13:47:57 GMT

If your data library points to a database -- not a folder of SAS data sets -- then NOBS might might not be available in the metadata that SAS can see. Instead, you might need to use PROC SQL with COUNT(*) or issue a database-specific query using PROC SQL and the CONNECT clause.

Also, if the library is a folder of SAS files but they are VIEW type and not DATA, then you won't be able to see the record count in the metadata.