Row by Row operation in a dataset

[bhu]

I have a dataset ""table_names which contains table names / dataset names.

I want to take each table name at a time and count the number of rows in that table and display.

Here is the example of my table.

DATA table_names;
length name $32 ;
input name;
DATALINES ;
tablea
tableb
tablec
;
RUN;

I want result like this assuming that tablea has 30 rows, tableb has 45 rows and tablec has 50 rows:

name rowcount

tablea 30

tableb 45

tablec 50

[Reeza]

Are these SAS data sets? Are they in a single library or multilple libraries?

There are a set of tables, dictionary tables, that hold this type of data so it's super easy to query that table. The table for this would be sashelp.vtable.

[bhu]

The datasets are not SAS datasets, they are sql tables. I am accessing sql tables through sas and counting them. 

[novinosrin]

with SAS access lib-name engine, you can treat the db tables as datasets and play with hashes

[Reeza]

Which DB? Can you hit their dictionary tables? Otherwise, the solution is to write a small macro that counts each file and appends it to a dataset. You can then use CALL EXECUTE to run the macro multiple times.

[ballardw]

---

@bhu wrote:

The datasets are not SAS datasets, they are sql tables. I am accessing sql tables through sas and counting them. 

---

Do you have SAS/Access to use those tables?

Many DBMS have their own set of tables containing meta data and you may be able to query the source database(s) about the properties of data tables.

[novinosrin]

data tablea tableb tablec;
x='blahblah';
run;




DATA table_names;
length name $32 ;
input name;
DATALINES ;
tablea
tableb
tablec
;
RUN;


data want;
if _n_=1 then do;
if 0 then set sashelp.vtable(keep=memname nobs);
   dcl hash H (dataset:"sashelp.vtable(keep=libname memname nobs where=(libname='WORK'))") ;
   h.definekey  ("memname") ;
   h.definedata ("memname","nobs") ;
   h.definedone () ;
   end;
set table_names;
if h.find(key:upcase(name))=0;
run;

[bhu]

This works fine for sas datasets inside the WORK library. Changing the libname to SQL, shows 0 rows for all tables in sashelp.vtable.

[Reeza · Accepted solution]

This can get you started. You'll likely need to make changes to account for a library reference or change how the names are passed.

1. Create a macro that counts the number of records in a single data set

2. Append that into master data set

3. Drop temporary tables

4. Run for each table in list

Note that this will create it into a table called myRecs. You can customize that name.

*macro to count the number of records;
%macro countRecs(tableName= , dset=);

proc sql noprint;
create table _temp as
select "&tableName" as tableName length=32, count(*) as nRecs
from &tableName;
quit;

proc append base=&dset data=_temp;
run;

%*drop table to avoid errors;
proc sql noprint;
drop table _temp;
quit;

%mend;

*data set with list of table names;
data demo;
input tableName $32.;
cards;
sashelp.class
sashelp.cars
sashelp.heart
;
run;

*run macro for each data set name;
data run;
set demo;

*creates string that looks like macro call;
str = catt('%countRecs(tableName=', tableName, ', dset=Myrecs);');
call execute(str);

run;

---

@bhu wrote:

I have a dataset ""table_names which contains table names / dataset names.

I want to take each table name at a time and count the number of rows in that table and display.

Here is the example of my table.

DATA table_names;
length name $32 ;
input name;
DATALINES ;
tablea
tableb
tablec
;
RUN;

I want result like this assuming that tablea has 30 rows, tableb has 45 rows and tablec has 50 rows:

name rowcount

tablea 30

tableb 45

tablec 50

---

[bhu]

Thank you all for replying and get the solution.

[MichaelLarsen]

This is not because the accepted solution is not fine.

I just found an opportunity to try out a perfect example of using the DOSUBL function 

%macro countobs(table);
  %global rows;
  %let rows=0;
  proc sql noprint;
    select count(*) format=12. into : rows trimmed
    from &table. ;
  quit;
%mend;

data tables;
  length table $41;
  input table;
  rc = dosubl(catt('%countobs(', table, ' )') );
  rows = input(symget('rows'),12.);
  drop rc;
  cards;
sashelp.class
sashelp.cars
;
run;