DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

 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?


Accepted Solutions
Solution
‎04-10-2018 08:13 AM
Community Manager
Posts: 3,383

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

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.

View solution in original post


All Replies
Super User
Posts: 9,924

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

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

Posted in reply to KurtBremser

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

Super User
Posts: 9,924

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


@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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

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

Posted in reply to KurtBremser

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

Super User
Posts: 9,924

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

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

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 NAMENobs
T1.
T2.
T3.
Solution
‎04-10-2018 08:13 AM
Community Manager
Posts: 3,383

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

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.

Super User
Super User
Posts: 9,441

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

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 103 views
  • 0 likes
  • 4 in conversation