🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 01-09-2008 09:17 AM
(110946 views)
Is there a simple way to get a count of the total number of records in a table, perhaps when using Proc SQL?
Thanks in advance....looking forward to your replies!
Thanks in advance....looking forward to your replies!
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You can use the function:
select count(*) as countrec from tablename;
within your proc sql.
If the table is a SAS dataset, it has its size in the header and is accessible with the NOBS= option:
%macro get_table_size(inset,macvar);
data _null_;
set &inset NOBS=size;
call symput("&macvar",size);
stop;
run;
%mend;
This method takes O(k) time (that is constant time) and doesn't require scanning through the table, O(n), to count the records. Use it like this:
%let reccount=;
%get_table_size(sashelp.cars,reccount);
%put &=reccount;
10 REPLIES 10
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You can use the function:
select count(*) as countrec from tablename;
within your proc sql.
If the table is a SAS dataset, it has its size in the header and is accessible with the NOBS= option:
%macro get_table_size(inset,macvar);
data _null_;
set &inset NOBS=size;
call symput("&macvar",size);
stop;
run;
%mend;
This method takes O(k) time (that is constant time) and doesn't require scanning through the table, O(n), to count the records. Use it like this:
%let reccount=;
%get_table_size(sashelp.cars,reccount);
%put &=reccount;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Mohamed,
Thanks for your reply! That worked for what I wanted to know about my dataset.
Thanks for your reply! That worked for what I wanted to know about my dataset.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If the table is a SAS dataset, it has its size in the header and is accessible with the NOBS= option:
%macro get_table_size(inset,macvar);
data _null_;
set &inset NOBS=size;
call symput("&macvar",size);
stop;
run;
quit;
%mend;
This method takes O(k) time (that is constant time) and doesn't require scanning through the table, O(n), to count the records.
To make the macro more robust, you would want to add a conditional wrapper:
%if %sysfunc(exist(&inset)) %then %do;
and then have some appropriate %else %do; for when the &inset doesn't "exist".
I'm not sure if there is a generic way to distinguish a permanent SAS dataset from a database table. I don't know if NOBS= will work for a database (Oracle, DB2, Sybase, Access, SQL Server, MySQL, etc. ). Message was edited by: Chuck
%macro get_table_size(inset,macvar);
data _null_;
set &inset NOBS=size;
call symput("&macvar",size);
stop;
run;
quit;
%mend;
This method takes O(k) time (that is constant time) and doesn't require scanning through the table, O(n), to count the records.
To make the macro more robust, you would want to add a conditional wrapper:
%if %sysfunc(exist(&inset)) %then %do;
and then have some appropriate %else %do; for when the &inset doesn't "exist".
I'm not sure if there is a generic way to distinguish a permanent SAS dataset from a database table. I don't know if NOBS= will work for a database (Oracle, DB2, Sybase, Access, SQL Server, MySQL, etc. ). Message was edited by: Chuck
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Chuck,
Interesting.... and thank you for the information. I'll put that to good use as time progresses.
Interesting.... and thank you for the information. I'll put that to good use as time progresses.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
in proc sql, you could also go against a table's metadata in dictionary.tables to get the nobs in a data set:
proc sql;
select nobs
from dictionary.tables
where libname eq 'SASHELP'
and memname eq 'ZIPCODE';
quit;
this approach avoids actually reading the data set. if you're looking at a very large table, this can save lots of time; if you're accessing a remote library, it should also save time. this only appears to work for sas data sets and not for databases (sql, mysql, oracle, etc).
i've also been unsuccessful with my attempts to access the view sashelp.vtable:
data _null_;
set sashelp.vtable;
where libname eq 'SASHELP'
and memname eq 'ZIPCODE';
put nobs;
run;
proc sql;
select nobs
from dictionary.tables
where libname eq 'SASHELP'
and memname eq 'ZIPCODE';
quit;
this approach avoids actually reading the data set. if you're looking at a very large table, this can save lots of time; if you're accessing a remote library, it should also save time. this only appears to work for sas data sets and not for databases (sql, mysql, oracle, etc).
i've also been unsuccessful with my attempts to access the view sashelp.vtable:
data _null_;
set sashelp.vtable;
where libname eq 'SASHELP'
and memname eq 'ZIPCODE';
put nobs;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
And thank you also, Richard. So many ways to do the same thing!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have no knowledge of macros, so I would like to know how to use the get_table_size macro.
If I have a data step:
data new_table;
set old_table;
run;
is it possible to call the macro in the same data step and assign the new_table number of rows to a variable nRows.
I would like to see the macro call:
nRows=%get_table_size( ?, ? ); /* Something like that? */
If I have a data step:
data new_table;
set old_table;
run;
is it possible to call the macro in the same data step and assign the new_table number of rows to a variable nRows.
I would like to see the macro call:
nRows=%get_table_size( ?, ? ); /* Something like that? */
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Chuck,
on a more basic level, what does the "&macvar" stand for? is this the name of the new variable you are creating which contains the total # of records?
thanks,
abc
on a more basic level, what does the "&macvar" stand for? is this the name of the new variable you are creating which contains the total # of records?
thanks,
abc
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
for sas information about a table in a dbms, see corresponding rows in sashelp.vmember and sashelp.vviews.
However, each dbms has its database information files equivalent to dictionary.tables. these probably could advise number of rows and columns (last time stats were collected). these resources are database dependant.
PeterC
However, each dbms has its database information files equivalent to dictionary.tables. these probably could advise number of rows and columns (last time stats were collected). these resources are database dependant.
PeterC
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Since this is an EG forum, I'll mention that in EG you can right-click on a table and see the number of rows under Properties in the General pane. If it's a DMBS table and not a SAS data set, by default this will say unknown. You can change that under Tools \ Options \ Data General then check "Always obtain the total record count for DBMS tables". There could be performance issues with this, FYI, which is why it's not checked by default.