topic Re: Can SAS automatically discover Access db metadata? in SAS Data Science

Can SAS automatically discover Access db metadata?

kjohnsonm — Wed, 02 Dec 2015 17:32:29 GMT

Hello all,

I have a project to mine all my network shares. To this end I have opened all my SAS data sets, all my DBF data sets, etc, now I am looking at Access DB files. My question is, can SAS automatically discover Access db metadata? I have a master metadata file I want to add all my MDB metadata too. The variables I am most interested in are: var PATH FILE EXT MEMNAME MEMLABEL TYPEMEM NAME LENGTH VARNUM LABEL; I of course have the path, file, & extension already from my file system search and have the remaining fields from reading proc contents for all of my files.

I also can use my file list to get just my mdb files, I am a little fuzzy at this point. I have read that VBA can do it, but would prefer to stay in SAS if possible. To my best understanding at this point “proc import” needs to know the table names to move forward form here, but that means opening 500 files by hand. Error prone…

(two extensions down, 609 to go.... good grief Charlie Brown.) -Keith Johnson

PS if there are any others that are working on a SAS project like this and have other extensions already done a shout out would be gladly welcomed.

Re: Can SAS automatically discover Access db metadata?

SASKiwi — Wed, 02 Dec 2015 18:35:50 GMT

If you want the metadata for Access databases using SAS you need to be able to assign a LIBNAME pointing at them. To do that you need to have SAS/ACCESS to PC Files installed and licensed. Is this the case? If so check out the link below

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n1wvmggexroxgyn17rp61jml3cvn.htm

Re: Can SAS automatically discover Access db metadata?

kjohnsonm — Wed, 02 Dec 2015 23:11:00 GMT

* --- only do this one time to make sure all_files_metadata is empty ---;
proc datasets library = work ;
delete all_files_metadata;
run;quit;

/*I get the SAS metadata just fine with this macro:*/

%macro read_sas_contents(MyPath, MyFileNoExt, MyExt);

%let MyExt = %upcase(&MyExt);

%if &MyExt = SAS7BDAT %then

%do;

libname CLibRef "&MyPath";

proc contents data=CLibRef.&MyFileNoExt. out=temp_metadata1;

run;

data temp_metadata;

set temp_metadata1;

length path $255. file $255. ext $20.;

path="&MyPath.";

file="&MyFileNoExt.";

ext="&MyExt.";

run;

proc datasets library=work ;

append base=all_files_metadata data=temp_metadata force ;

run;quit;

%end;

%mend read_sas_contents;

*and I am working on this code as a frame work;

%macro read_mdb_contents(MyPath, MyFileNoExt, MyExt);

%let MyExt = %upcase(&MyExt);

%if &MyExt = MDB or &MyExt = ACCDB %then

%do;

LIBNAME MyMdb ACCESS PATH="&MyPath.\&MyFileNoExt..&MyExt.";

proc contents data=MyMdb.&MyFileNoExt. out=temp_metadata1;

run;

data temp_metadata;

set temp_metadata1;

length path $255. file $255. ext $20.;

path="&MyPath.";

file="&MyFileNoExt.";

ext="&MyExt.";

run;

%end

;

%mend read_mdb_contents;

/*I know that access does not work with the proc contents, however queries like this: */

proc sql;
SELECT * FROM MyMdb.MSysObjects WHERE Type = 1 OR Type = 4
;
quit;

/*don't work either, and this does not seem to help*/

LIBNAME mymdb LIST;

/*I mean i get data from LIST but i am not getting tables from the libname list or fields i need. I am just not sure where to turn...*/

Re: Can SAS automatically discover Access db metadata?

kjohnsonm — Wed, 02 Dec 2015 23:13:27 GMT

PS SASKiwi, Thank you for your time -Keith

Re: Can SAS automatically discover Access db metadata?

PGStats — Wed, 02 Dec 2015 23:17:10 GMT

Typically you would define a libname pointing to your database and then get the metadata from pseudo tables dictionary.tables or dictionary.columns or from proc datasets:

LIBNAME AC ACCESS PATH='C:\PCFData\Demo.accdb' access=readonly;

proc sql;
select memname from dictionary.tables
where libname = "AC";
quit;

Re: Can SAS automatically discover Access db metadata?

kjohnsonm — Thu, 03 Dec 2015 00:20:02 GMT

            LIBNAME MyMdb ACCESS PATH="&Mydir.\&Myfile..mdb" access=readonly;
            proc sql;
               select memname from dictionary.tables
/*               select * from dictionary.columns*/
               where libname = "MyMdb";
           quit;

based on your post I tired the above, I get:

330                  select memname from dictionary.tables
331 /*              select * from dictionary.columns*/
332                  where libname = "MyMdb";
NOTE: No rows were selected.
333              quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

...for both selects shown above, the lib* referance you indicate works just fine.

*access=readonly;

Re: Can SAS automatically discover Access db metadata?

SASKiwi — Thu, 03 Dec 2015 01:31:30 GMT

Try where libname = "MYMDB" - uppercase is required.

Re: Can SAS automatically discover Access db metadata?

PGStats — Thu, 03 Dec 2015 02:35:09 GMT

Sorry, I should have mentioned that.

Re: Can SAS automatically discover Access db metadata?

kjohnsonm — Thu, 03 Dec 2015 03:53:00 GMT

No problem. I will try it in the morning. -KJ

Re: Can SAS automatically discover Access db metadata?

kjohnsonm — Thu, 03 Dec 2015 16:08:20 GMT

I tried it again with both all upper case WORKS! I like to use upper lower case for my variables to distinguish them. Thank you. Sorry it took so many tries...

Re: Can SAS automatically discover Access db metadata?

kjohnsonm — Thu, 03 Dec 2015 23:19:39 GMT

A better general solution might be given any LIBNAME you can access (execute a correct LIBNAME command and see the proc sql below):

LIBNAME MYMDB ACCESS PATH="D:\Mydir\MyFile.mdb" access=readonly;

 
PROC SQL;
/* for each of these lines executed, you will find a list of metadata info in your log given you have an active libname*/
describe table dictionary.members;
/*describe table dictionary.columns;*/
/*describe table dictionary.tables;*/

/* only lasting question I have is what is this "dictionary.catalog" supose to help with?  I didn't see any "log" results here with my access db*/
/*describe table dictionary.catalogs;*/

/*The vcolumn is a little silly affectivly select *  like we don't already know this...   8) */
/*describe view sashelp.vcolumn;*/
QUIT;

/*~~~~~~~~~~~log results here~~~~~~~~~~~*/

654 LIBNAME MYMDB ACCESS PATH="D:\Mydir\MyFile.mdb" access=readonly;

NOTE: Libref MYMDB was successfully assigned as follows:

Engine: ACCESS

Physical Name: D:\Mydir\MyFile.mdb

655 /*Then execute the following code uncommenting just one of the lines, for example I show the results from dictionary.members*/

656 PROC SQL;

657 describe table dictionary.members;

NOTE: SQL table DICTIONARY.MEMBERS was created like:

create table DICTIONARY.MEMBERS

(

libname char(8) label='Library Name',

memname char(32) label='Member Name',

memtype char(8) label='Member Type',

dbms_memtype char(32) label='DBMS Member Type',

engine char(8) label='Engine Name',

index char(3) label='Indexes',

path char(1024) label='Pathname'

);

/*~~~~~~~~~~~end log results here~~~~~~~~~~~*/

proc sql;
/*create table mytables as*/
select

 

/*be sure to update dictionary.___  to whatever you used above and match the feild names selected too your log results*/


                              libname,
                              memname,
                              memtype,
                              dbms_memtype,
                              engine,
                              index,
                              path
from dictionary.members

/* be sure to use upper case for your call here.  I think it does not matter in the lib ref above, (but not sure)*/
where libname=upcase('mymdb');

/*run;*/

quit;