EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.


Cool Tips & Tricks - 4
Grab all data(size, records) from a folder or database

Here is the code to get all the SAS table names, records, size of the datasets in a libname:
libname  db1  ....;
 proc sql; 
        create table tab1 as 
        select distinct libname, memname, nobs, obslen, 
        nobs*obslen/(1024*1024) as Mega_Bytes 
        from dictionary.tables 
        where libname='db1'; 
quit; 
If the libname is from some other databset, e.g. SQL database,
and you might not able to get the number of records and size of the datasets directly from the previous code. here is the way you can get:
proc sql;
select count(*) into: obs from tab1;quit;
proc sql; 
select distinct memname into: mem1- from tab1; 
/*if not sure num of memnames, don't need to list*/
quit;

data data1 ;
length Table $75.   records 8;
if _n_=1 then delete;
run; 

%macro tt;
%do i=1 %to &obs.;

proc sql;
create table Temp
as select "&&mem&i." as Table,COUNT(*) as records
from db1.&&mem&i.
;quit;

proc append base=data1 data=Temp force;run;
%end;
%mend;

%tt;

proc sql;
create table COLUMNS as
select 
 libname  label='Library Name',
 memname label='Member Name',
 memtype  label='Member Type',
 name
 from dictionary.columns
where libname='db1';
quit;  
From Aginity, we can use the following code to get the data size and skewness for the netezza dataset :
SELECT  * FROM _V_TABLE  
 limit 10;
 
 SELECT  * FROM _v_table_storage_stat 
 limit 100;

drop table datasize1;
create table datasize1 as
SELECT  owner,tablename, cast(createdate as date) as date,
used_bytes/pow(1024,3) as used_gb, skew 
FROM  _v_table_storage_stat
distribute on random
;

--also you can specify the time

select substring(tablename, 1, instr(tablename, '_')) prefix, 
       to_char(CREATEDATE::DATE, 'YYYYMM') table_month,
       sum(round(used_bytes/pow(1024,3), 3)) as used_gb, 
       count(*) 
from _v_table_storage_stat 
where OBJTYPE = 'TABLE' 
and table_month <'2017' -- < '2017' -- really old tables
group by 1,2
order by 1,3
;
 
Related links:
Continue to next: SAS Cool Tips & Tricks of Proc SQL: Rank a variable   SAS tutorial home
Back to: SAS Cool Tips & Tricks: Moving Average, summarize to the nearest 0.25%   Statistics tutorial home