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 file, 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.
Have you tried to include the DATATABLE option? The specs for the proc include:
PROC IMPORT
|
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
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
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).
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.