topic Re: Importing a paradox db file into SAS in SAS Data Management

Importing a paradox db file into SAS

tzy0147 — Fri, 13 Jan 2017 19:59:14 GMT

Hello,

I have a problem of importing a paradox db file into SAS. My db file includes several tables and I just want to import specific tables into SAS. The offical guide gives an example like this:

This example imports the SAS data set, WORK.CUSTOMER, from the Paradox DB ﬁle, customer.db, on a local system.
PROC IMPORT OUT=WORK.CUSTOMER

FILE="&tmpdir.customer.db"

DBMS=DB REPLACE;

RUN;

It seems only one table is in the db file so it can be import into a data set. But it does not work if there are several tables in the db file. Wondering if anyone can help me figure it out.

Much appreciated.

Re: Importing a paradox db file into SAS

art297 — Fri, 13 Jan 2017 21:25:07 GMT

Have you tried to include the DATATABLE option? The specs for the proc include:

PROC IMPORT
DATAFILE=<'filename'>|DATATABLE=<'tablename'>
<DBMS>=<data-source-identifier>
<OUT>=<libref.SAS data-set-name> <SAS data-set-option(s)>
<REPLACE>;

You indicated that DBMS=DB worked, but I didn't see that in the documentation. It does, however, include DBMS=Paradox

You could also use the libname access method instead. That would get all of the tables without having to specify them.

HTH,

Art, CEO, AnalystFinder.com

Re: Importing a paradox db file into SAS

rogerjdeangelis — Sat, 14 Jan 2017 02:55:22 GMT

SOAPBOX ON
This would be trivial if SAS had not disabled all
paradox engines and paradox sql connections.
Engines are a lot more powerful than
'import/export' because they are seamless inside and
outside the datasteps. However it is very intensive
programming to provide powerful constructs like:

select member from sashelp.vtable where libname='PARADOX';
SOAPBOX OFF

Unfortunately it looks like SAS has disabled the old dbpdox engine.
The following no longer  works.

libname PBOX dbpdox 'c:\windows\desktop\test' ;

data A ;
   set PBOX.ADRHEAD ;
run ;

I get the following log:

26   libname PBOX dbpdox 'c:\windows\desktop\test' ;

NOTE: Libref PBOX was successfully assigned as follows:
      Engine:        DBPDOX
      Physical Name: c:\windows\desktop\test

I am not fond of the import/export replacement so
I look to Python, Perl and R packages.

I will post an R solution tomorrow, using the R packages RODBC and sqldf.

WORKING CODE (something like)

myDB <- odbcConnect("usrdox",uid="admin",pwd="");
for (table in tables) {
    sysval(table) <- sqlFetch(myDB, "sysval(table)") }

I also have code which creates a dsn programmatically, thanks toRichard DeVenezia

Re: Importing a paradox db file into SAS

tzy0147 — Sat, 14 Jan 2017 06:54:18 GMT

Thank you for your reply. However, the log says ERROR: Cannot specify both FILE= and TABLE=. I am wondering what is the libname access method? Thank you.

Re: Importing a paradox db file into SAS

art297 — Sat, 14 Jan 2017 15:44:17 GMT

You can read about libname access at: http://statdist.its.uu.se/sas/Dokument/SASacpcref.pdf?Company=SAS

However, I don't see any mention of Paradox in that document's section regarding libname access.

I don't license Access for PC File Formats thus, unfortunately, can't be of any help.

My comment about the engine being called Paradox is apparently only applicable to PROC EXPORT.

Hopefully, someone from SAS will see this thread and supply the info that you need (if it exists).

Re: Importing a paradox db file into SAS

rogerjdeangelis — Sat, 14 Jan 2017 19:13:33 GMT

SAS did a better job than R.
Neither R nor SAS could convert Paradox 7 tables?
Of the Paradox 4 and 5 tables SAS read 4 and R read 3

The issue with R may be a lack of 32bit Paradox ODBC drivers?

* you can get a sample paradox database here;
* these are good samples and have many variable types (4.x,5.x,7.x);
http://www.sportamok.com/development/download;

* Roland Rashleigh-Berry provided the directory code, may he RIP;
* the old guard is dying off;
* don't forget the blank after db;

* put the members of directory d:\db in macro var dbs;

%let dbs= %utl_dir(d:\db\*.db );
%put &=dbs;

DBS=biolife.db  COUNTRIES.DB  country.db  events.db  FILMS.DB  venues.db

Uppercase Paradox 7 tables could not be read by SAS or R but Stattransfer could read them.

HAVE A PARADOX DATABASE WITH THESE TABLES  (Paradox 4, 5 and 7)
===============================================================

biolife.db
country.db   * R could not read this one but SAS could
events.db
venues.db

WANT SAS DATSETS
================

BIOLIFE.SAS7BDAT
COUNTRY.SAS7BDAT
EVENTS.SAS7BDAT
VENUES.SAS7BDAT


SAS SOLUTION (cannot read Paradox 7 tables, however Stat Transfer can)

options validvarname=upcase;;
data _null_;

  tables= "%utl_dir(d:\db\*.db )";
  put tables=;
  do tbl=1 to countc(tables,'.');

     table=scan(scan(tables,tbl,' '),1,'.');
     * PARADOX 7;
     if upcase(table) not in ('COUNTRIES', 'FILMS', 'CARS' 'RESTTEMP') then do;
         call symputx('table',table);
         put table=;
         rc=dosubl('
              proc import out=work.&table
              file="d:/db/&table..db"
              dbms=db replace;
              run;quit;
            ');
     end;
   end;

run;quit;


* As soon as R implements 'write_sas' this code will become
  very simple - the save will rePlaced by something like:
  for (table in tables) {write_sas(assign(table,sqlFetch(myDB, table),paste("d:/db/",table,".sas7bdat")};

However stattransfer and SAS seem to do a better job;

Howver it is rather complex now;

* I don't have a 64 bit driver so I need to use
  the 32bit version of R, note R comes with both versions;

* this works;
* you may need 32bit SAS. I used 32bit R;
* create R dataframe from db table;
%macro todb(dummy);

 %let dbs=biolife.db country.db event.db venues.db;

  %do tbl=1 %to %sysfunc(countc(&dbs,%str(.)));

    %let table=%sysfunc(strip(%scan(%scan(&dbs.,&tbl.,%str( )),1,%str(.))));

    %put &=table.;

    /* %let table=events;  */

    * you need to use double quotes so macros and macro variables are resolved;
    %utl_submit_r32("
    source('C:/Program Files/R/R-3.3.2/etc/Rprofile.site', echo=T);
    library(RODBC);
    library(sqldf);
    '&table';
    myDB<-odbcDriverConnect('Driver={Microsoft Paradox Driver (*.db )};DriverID=538;
    Fil=Paradox 5.X;DefaultDir=d:/db/;Dbq=d:/db/;CollatingSequence=ASCII');
    '&table';
    &table <- sqlFetch(myDB, '&table.');
    '&table';
    save(&table,file='d:/rds/&table..rda', compress = FALSE);
    &table;
    close(myDB);
    ");

    %utl_submit_wps64("
    options set=R_HOME 'C:/Program Files/R/R-3.3.2';
    libname wrk '%sysfunc(pathname(work))';
    proc r;
    submit;
    source('C:/Program Files/R/R-3.3.2/etc/Rprofile.site', echo=T);
    load(file = 'd:/rds/&table..rda');
    &table.;
    endsubmit;
    import r=&table. data=wrk.&table.;
    run;quit;

    ");
  %end;

%mend todb;

%todb;



/*T807320 Function-style macro to return a list of members of a directory

dir.sas  - Function-style macro to return a list of members of a directory
           on a WINDOWS platform according to the file pattern you supply.
           If you supply just the directory name then all members are
           listed. This runs the MSDOS command in the form "dir /B mydir"
           Usage: %let dirlist=%dir(C:\utilmacros);
           %let dirlist=%dir(C:\utilmacros\*.sas);

%macro utl_dir(dir);
  %unquote(%utl_readpipe(dir /B %sysfunc(dequote(&dir))))
%mend utl_dir;


%macro utl_readpipe(command);
  /* Roland macro */
  %local fname fid str rc res err;
  %let err=ERR%str(OR);
  %let rc=%sysfunc(filename(fname,&command,pipe));
  %if &rc NE 0 %then %do;
    %put &err: (qreadpipe) Pipe file could not be assigned due to the following:;
    %put %sysfunc(sysmsg());
  %end;
  %else %do;
    %let fid=%sysfunc(fopen(&fname,s,80,b));
    %if &fid EQ 0 %then %do;
  %put &err: (qreadpipe) Pipe file could not be opened due to the following:;
  %put %sysfunc(sysmsg());
    %end;
    %else %do;
      %do %while(%sysfunc(fread(&fid)) EQ 0);
        %let rc=%sysfunc(fget(&fid,str,80));
        %let res=&res%superq(str);
      %end;
      %qtrim(&res)
      %let rc=%sysfunc(fclose(&fid));
      %if &rc NE 0 %then %do;
  %put &err: (qreadpipe) Pipe file could not be closed due to the following:;
  %put %sysfunc(sysmsg());
      %end;
      %let rc=%sysfunc(filename(fname));
      %if &rc NE 0 %then %do;
  %put &err: (qreadpipe) Pipe file could not be deassigned due to the following:;
  %put %sysfunc(sysmsg());
      %end;
    %end;
  %end;
%mend utl_readpipe;