topic Re: Count for 50 or 100 tables with one shot in SAS Programming

Count for 50 or 100 tables with one shot

jayanth_sas — Mon, 28 Oct 2013 13:18:58 GMT

Hi,

I have some 50 tables and i need table count of each. Apart from running select count (*) from table name.

Do we have any other method that will give in single shot 50 or 100 tables count and table name with proper layout.

Can anyone suggest on this plz.

Header 1	Header 2
table name1	count1
table name2	count2
table name3	count3

Re: Count for 50 or 100 tables with one shot

Cynthia_sas — Mon, 28 Oct 2013 13:41:57 GMT

Hi:

This is something you could do quite easily by reading the DICTIONARY.TABLES dataset. There are a lot of previous posts about finding out information about your table and columns using this technique. In addition, there are Tech Support notes like this:

http://support.sas.com/kb/25/436.html, and documentation topics like this one:

http://support.sas.com/documentation/cdl/en/sqlproc/65065/HTML/default/viewer.htm#n0cqcq4fhom7qtn17t7zkfdkxp4f.htm (see example 6)

The variables in DICTIONARY.TABLES include a variable called NOBS and another called CRDATE -- which are the count of physical observations and the creation date, respectively.

cynthia

Re: Count for 50 or 100 tables with one shot

jayanth_sas — Mon, 28 Oct 2013 17:00:52 GMT

Hi Cynthia,

Thank you for your information. But i am little worried to use dictionary. Because there seems to be every time i need to select respective table name in find for your contents. Actually i dont want contents at all. I only require table name and respective count of that table like 500 observations. if number of tables are 50 or >50 at single shot how i can get the output which shows 50 table names with respective counts in proper lay out.

Re: Count for 50 or 100 tables with one shot

Reeza — Mon, 28 Oct 2013 17:10:29 GMT

Open up sashelp.vtable and take a look at the columns and run the following and see what it outputs.

Proc print data=sashelp.vtable (obs=20);

var libname memname nobs nlobs nvar;

run;

Re: Count for 50 or 100 tables with one shot

jayanth_sas — Mon, 28 Oct 2013 17:28:17 GMT

Hi reeza,

Its nice. But i have created a new library and i only want count from that particular library and not from any other (rawdata,gismaps ets) with out mentioning obs. Can you plz suggest me

Re: Count for 50 or 100 tables with one shot

Tom — Mon, 28 Oct 2013 17:40:29 GMT

I am not sure what your issue is. Just run the query and use the data in whatever way you want.

Use a WHERE clause the select the tables you are interested in.

libname mylib 'mypath';

proc sql ;

select memname,nlobs

from dictionary.tables

where libname='MYLIB'

;

quit;

Re: Count for 50 or 100 tables with one shot

jayanth_sas — Mon, 28 Oct 2013 18:18:55 GMT

Thank you Tom.

Got it