CODEWITHSUNDEEP
sas
Binnnnn5
Binnnnn5

Reputation: 187

How to extract some information from all of the tables in a library?

How to PROC SQL to extract information from a library named as DATA_IN?

The expected information including:

  1. The name of the tables.

  2. The total number of variations contained in the tables.

  3. The total observation number contained in the tables.

    If the table contains "Subject_ID or Visit_Num", contains mark as 1, if not, mark as 0.

I have made attempt like this:

proc sql;
    create table table_select as
    select libname, memname, nobs, nvar
    from dictionary.tables
    where libname = 'DATA_IN'
    order by nobs descending
;

But still don' t know how to tell if a table contains column "Subject_ID or Visit_Num" and mark it with "1" or "0".

Thank you for your help!

Upvotes: 0

Views: 390

Answers (1)

Richard
Richard

Reputation: 27526

You need a query that joins DICTIONARY.TABLES with DICTIONARY.COLUMNS

Example:

proc sql;
  create table want (label='sashelp tables with a column named "name" or "MSRP"') as 
  select 
    tables.libname
  , tables.memname
  , tables.nobs
  , tables.nvar
  , not missing(mark.memname) as has_name_or_msrp
  from
    DICTIONARY.TABLES
  left join 
    (select distinct memname from DICTIONARY.COLUMNS
     where columns.libname = 'SASHELP'
       and upcase(columns.name) in ('NAME', 'MSRP')
    ) as mark
  on 
    tables.memname = mark.memname
  where 
    tables.libname = 'SASHELP' 
    and memtype = 'DATA'  /* restrict to only tables */
  order by 
    nobs descending
  ;

  data _null_;
  run;

Upvotes: 1

Related Questions