Tag: data null

SAS: Read files in folder and compile list of filenames

In a prvious post, “Filename: Create SAS dataset of folder hierarchical structure and file list“, I have described a way to extract filenames in a folder or subfolder to a file. Here is a different way of doing it.

Reference: Reading and Processing the Contents of a Directory by Ben Cochran, The Bedford Group, Raleigh, NC. https://www.mwsug.org/proceedings/2012/S1/MWSUG-2012-S128.pdf

The following SAS codes get the filenames from L:\DaraFiles directory and pass them to a macro variable called varlist.

%let varlist =;
data null;
rc =filename("mydir", "L:\DataFiles");
did = dopen("mydir");
if did > 0 then do;
num =dnum(did);
do i = 1 to num;
fname =dread (did, i );
put fname = ;
call symput('varlist', trim(resolve('&varlist'))||' '||trim(fname));
end;
end;
run;
%put &varlist.;

Key Functions:

  • filename: assign the directory to a alias.
  • dopen: open the directory and get the directory identifier;
  • dnum: get the number of member in the directory;
  • dread: get the filenname of the directory member;

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*/;