@Ivan555
sashelp.vtable is a view that points to one of the actual SAS dictionary tables. You can either query the view or the dictionary table directly. You can also figure out where the actual dictionary table "lives" by looking into the view definition.
proc sql;
describe view sashelp.vtable;
quit;
28 proc sql;
29 describe view sashelp.vtable;
NOTE: SQL view SASHELP.VTABLE is defined as:
select *
from DICTIONARY.TABLES;
30 quit;
To then get the full definition of this table you can execute
proc sql;
describe table dictionary.tables;
quit;
SAS dictionary tables are READ ONLY. But you can of course create your own table by joining the dictionary table with additional information you're deriving yourself.
You're after a row count and the file size for database tables. This is information you need to retrieve by querying the database directly.
I don't know what filesize means in the context of a database so you have to work this out for yourself. Below sample code demonstrating how you could get the row counts. Row counts are in the data bases I work mostly with not a table attribute and for this reason not available as dictionary information. It requires a SQL select count(*) against every single table so that's going to be a costly process.
"will it be fair if I'll ask you to do whole task for me?"
Not really, no. That's what many of us are doing as a paid job so it's not for free. But well, I got inspired so below should already give you quite a lot.
Below code sample executes against SAS library WORK but it should work for any library pointing to a database. You will have to amend the code to fit your needs but it's likely already 80% of the solution for you.
options ls=max ps=max;
/* create some tables in library work */
data work.class work.class2;
set sashelp.class;
output work.class;
if _n_<5 then output work.class2;
run;
/* create list of tables in Work */
proc sql;
create table tableList as
select l.engine, t.*
from
(select distinct libname, engine from dictionary.libnames where libname='WORK') l
inner join
dictionary.tables t
on l.libname=t.libname and t.memtype='DATA'
order by libname, memname
;
quit;
/* execute select count(*) against all tables in WORK */
%macro countRows(outtbl, libname, memname, engine);
%if %upcase(&memname) ne _THISCOUNT %then
%do;
%if &engine = V9 %then
%do;
/* do not execute a select count(*) against Base SAS tables */
data _thisCount;
length libname $8 memname $32 nlobs 8;
stop;
run;
%end;
%else
%do;
proc sql;
create table _thisCount as
select
"&libname" as libname length=8,
"&memname" as memname length=32,
count(*) as nlobs length=8
from &libname..&memname
;
quit;
%end;
proc append data=_thisCount base=&outtbl;
run;
proc datasets lib=work nolist nowarn;
delete _thisCount;
run;
quit;
%end;
%mend;
proc datasets lib=work nolist nowarn;
delete _rowCounts;
run;
quit;
data _null_;
set tableList;
call execute(cats('%countRows(_rowCounts,', libname, ',', memname, ',', engine, ')'));
run;
/* create new table by joining SAS dictionary table with result table from select count(*) */
data want;
if _n_=1 then
do;
dcl hash h1(dataset:'_rowCounts');
h1.defineKey('libname','memname');
h1.defineData('nlobs');
h1.defineDone();
end;
set tableList;
if h1.find()=0 then nobs=nlobs;
run;
proc print data= want;
/* var libname memname nobs nlobs;*/
run;
Please try to understand above code before asking for more.
Click image to register for webinar
