How to find count of each dataset

[_el_doredo]

Hello Experts,

I have a table in which column A contains dataset names. Now I need to create a new dataset with number of observation available in each dataset. I have provided my data below and my expected output as well.

Dataset_Name	Library
Product	REF_TAB
Sales	REF_TAB
Sales_History	REF_TAB
Payment	REF_TAB
Payment_history	REF_TAB

DATASET_NAME variable value is the name of the datasets. Now I need to find the number of observations of each dataset(no of observations in Product dataset, no of observations in Sales dataset and so on)

My expected output is like this

Dataset_Name	Library	Count
Product	REF_TAB	1,56,658
Sales	REF_TAB	1,86,698
Sales_History	REF_TAB	1,54,472
Payment	REF_TAB	3,22,669
Payment_history	REF_TAB	1,54,423

Thanks in advance

[Kurt_Bremser · Accepted solution]

proc sql;
create table want as
  select
    t1.library,
    t1.dataset_name,
    t2.nobs
  from have t1
  inner join dictionary.tables t2
  on upcase(t1.library) = t2.libname and
  upcase(t1.dataset_name) = t2.memname
;
quit;

[_el_doredo]

Thanks for your answer. However, it's not working. I am not able to fetch count using the code you provided.

[PaigeMiller]

What about it is not working? Please describe in detail (and please in the future don't tell us it is "not working" without explaining what is not working).

If there is an error in the log, please show us the log (not just the error messages) for this block of code. If the results are wrong, show us what is wrong and explain the results you are expecting.

[_el_doredo]

Sorry my bad. Actually, the code which provided here will work fine if library is in SAS. But library REF_TAB is assigned to a tera data. That's why count is not displaying when I ran the code. Is there any way we can fetch the tera data table count in SAS?

[PaigeMiller]

I don't think you can use that SAS code to count the number of records in a Teradata (or any other database) table. Perhaps @Kurt_Bremser has other suggestions for Teradata.

[Kurt_Bremser]

Upload your reference dataset to a temporary table in the DB. Run a similar query in explicit passthrough, using the DB's dictionary tools, and then download the result.

[Tom]

SAS does not know how many observations are in a foreign database.  And reading the links posted on other threads it does not look like Teradata knows either.  So you will have to issue queries to count the observations.  A separate query for each dataset.

You might use something like the loop in this macro:

https://github.com/sasutils/macros/blob/master/dslist.sas

[Kurt_Bremser]

I found this about getting rowcounts in Teradata: https://dataedo.com/kb/query/teradata/list-of-tables-by-the-number-of-rows 

[Tom]

Looks like the same idea posted before.  It depends on the table statistics being up to date.  So for a stable database with good management it would help.

But it would not help with views.

[AhmedAl_Attar]

Hi @_el_doredo 

Libraries based on SAS/ACCESS Interface to <RDBMS> (Oracle, Hive, Teradata, Snowflake,....etc) will not have all the table's characteristics/properties in the SAS dictionary tables/views, nor in the Data Set header portion.

So to get Row/Observation count for external <RDBMS> table, your two options are:

• Query the <RDBMS> internal dictionary/system tables (Efficient). For Teradata, check these links 

https://dbmstutorials.com/teradata/teradata_data_dictionary_queries.html

https://dataedo.com/kb/query/teradata/list-of-tables-by-the-number-of-rows

• Issue Select count(*) query for every table in your list (can be Inefficient)! Not desired for large tables, if the database does not save this info ! 

Hope this helps,

Ahmed