SAS: Create Meta Data from ODBC or ORACLE Data Engine

Metadata have the following table level summary information in the &prefix._tables.

  • libname: library name
  • memname: Member Name (table name)
  • maxlabel: longest label
  • maxvar: longest variable name
  • nobs: Number of Physical Observations ( for remote access, this field is null)
  • nvar: Number of Variables
  • num_character: number of Character Variables
  • num_nmeric: number of Numeric Variables

Metadata also contains the following variable level information in the &prefix._var.

  • libname: library name
  • memname: Member Name (table name)
  • name: Column Name (variable name)
  • type: Column Type (variable type: char, num)
  • length: Column Length (variable length)
  • npos: Column Position (variable position)
  • varnum: Column Number in Table (variable number in table)
  • label: Column Label (variable label)
  • format: Column Format
  • informat: Column Informat
  1. Oracle engine
libname ORACLEMETA oracle user=xxxxx password="xxxxx" path=parmp schema=schemaname;

/*** local library ***/
libname loc "X:\SAS\Data Source\Dictionary";

%macro dic(prefix=, lib=);
PROC SQL;
CREATE TABLE &prefix._tables AS
SELECT *
FROM dictionary.tables as one
where one.libname = "&lib." ;
QUIT;
data loc.&prefix._tables;
set &prefix._tables;
run;
proc sql;
create table varlist as
select distinct memname from &prefix._tables;
quit;
%mend dic;
%dic (prefix = SCHEMANAME, lib = ORACLEMETA);

%let varlist =;

data null;
set varlist;
call symput('varlist', trim(resolve('&varlist'))||' '||trim(memname));
run;

%put &varlist.;

%macro dic(prefix=, lib=, table=);
proc datasets library=work noprint;
delete &prefix.var;
quit;
%let i=1;
%do %while (%scan(&table., &i, ' ') ne );
%let dsn=%scan(&table., &i, ' ');
PROC SQL;
CREATE TABLE &prefix.&i. AS
SELECT *
FROM dictionary.columns as one
where one.libname = "&lib." and one.memname= "&dsn." ;
QUIT;
proc append base = &prefix.var data = &prefix.&i.;
run;
proc datasets library=work noprint;
delete &&prefix._&i.;
quit;
%let i = %eval (&i +1);
%end;
data loc.&prefix._var;
set &prefix._var;
run;
%mend dic;
%dic (prefix=SCHEMANAME, lib = ORACLEMETA, table = &varlist.);
  1. ODBC
  • /*** Set up ODBC engine to read SQL Server database in SAS ***/ 
    libname ODBCMETA odbc dsn=dsnname user=xxxx pwd=xxxxxx schema = dbo;
  • /*** Set up ODBC engine to read MySQL database in SAS ***/ 
    libname ODBCMETA odbc dsn="dsnname" user=xxxxxx pwd=xxxxxx /*cursor_type=dynamic*/;