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
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
The datasets are not SAS datasets, they are sql tables. I am accessing sql tables through sas and counting them.
with SAS access lib-name engine, you can treat the db tables as datasets and play with hashes
@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.
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;
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
Thank you all for replying and get the solution.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.