|
Jeff;
SAS can easily return the number of rows in a SAS data file because that value exists in the file header. So SAS simply has to pluck it from the metadata.
Not so with data from external databases. Also not so for SAS data views. For these, you will have to count the number of rows. Suppose you have a table in Access named Foo. (I'm going to make it easy on myself by using the ACCESS engine in the LIBNAME statement, but the principle applies with the ODBC engine.)
LibName mdbLib ".\test.mdb" ;
Data mdbLib.Foo ;
Do i=1 to 1e5 ;
j = put( i , words. ) ;
Output ;
End ;
Run ;
Data RowsInFoo ;
Set mdbLib.Foo( keep=i ) end=eof ;
Drop i ;
If eof then do ;
Rows = _N_ ;
Output ;
End ;
Run ;
LibName mdbLib clear ;
Now, SAS would have let me specify ...
Set mdbLib.Foo( drop=_all_ ) end=eof ;
which would have saved a little time. However, ODBC will probably not allow you to read a table with no columns; Jet won't. To bad. That would have saved some work.
Ed
Edward Heaton, Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1650 Research Boulevard, TB-286, Rockville, MD 20850-3195
Voice: (301) 610-4818 Fax: (301) 294-2085
mailto:EdHeaton@Westat.com http://www.Westat.com
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jeff
Sent: Monday, December 08, 2008 12:58 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: number of records of each dataset in a library
Thanks for response.
How about to odbc lib?
libname MPI odbc dsn=empi uid=&sysuserid schema=dbo readbuff=32767; I want to check #records in MPI. I tried
proc *sql*;
select memname, nlobs
from dictionary.tables
where libname=*'MPI'* and memtype=*'Data'*;
quit;
but this code return 0 records.
How Can fix this problem?
Thanks.
Jeff
Jeff
On Mon, Dec 8, 2008 at 11:48 AM, Akshaya Nathilvar < akshaya.nathilvar@gmail.com> wrote:
>
> By querying SASHELP.VTABLE:
> Proc sql;
> select memname 'Dataset Name',nobs '# of Records'
> from sashelp.vtable
> where libname=<lib> and memtype='DATA'; /* Libname should
> be in uppercase */ Quit;
>
> Akshaya
>
>
> On Mon, Dec 8, 2008 at 11:30 AM, Jeff <zhujp98@gmail.com> wrote:
>
>> I want to list nuber of records of each dataset in a library, The
>> resulting table shoud look like:
>>
>>
>> datasetname #rOfrecords
>> ds1 2345
>> ds2 209
>> ---
>>
>> Thanks,
>> Jeff
>>
>
>
| 
