BookmarkSubscribeRSS Feed
cbrotz
Pyrite | Level 9

Our company still uses Paradox.  They add data to tables from their Paradox processes and I import them into SAS EG. It appears that although the number of records is the same before and after the import, I cannot find certain data using "equal to" or "contains" etc.  This is not true for all the records.  It seems to be recently added records that are the main problem so just very strange results when we use the filter.  We are on EG 8.1.  It is a large file 15 mil records or so. Nothing fancy - just dragging the table in to do an import.  We also tried using the "import" dropdown and neither works.

 

Has anyone had any experience with Paradox import issues?

 

 

Thank you,

Chris Brotz


Capture.PNG
8 REPLIES 8
ballardw
Super User

You should provide examples of the values that are not found with your "equal to" or "contains" and peferably the code generated that uses those expressions or filters for those values

 

Some like causes could be case sensitivity "YES" does not match "yes", leading spaces " Yes" does not match "Yes" and sometimes language encoding comes up such as "á" instead of "a" .

If you have long strings of digits, such as account numbers, that are being treated as numeric the values may not be correct due to numeric precision issues if they 18 or so digits

cbrotz
Pyrite | Level 9

Thank you for the response.  I added the code generated from the import.  Yes it may have something to do with the format of the incoming data altough it appears correct.

rogerjdeangelis
Barite | Level 11
/* T1001420 SAS/R Importing a paradox database into SAS

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

There seem to be a lot of issues,especially with Paradox 7.
Some data types have issues in versions 5 and 6?


SAS/R Importing a paradox database into SAS

inspired by
https://goo.gl/f0UGW7
https://communities.sas.com/t5/Base-SAS-Programming/Importing-a-paradox-db-file-into-SAS/m-p/324832/highlight/false#M72138

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

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

* Roland Rashleigh-Berry 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;
* macros on end;
%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;
TomKari
Onyx | Level 15

A few issues that I've seen come up before on this kind of movement / conversion:

 

Character data

upper / lower case issues

unexpected spaces

unprintable characters

 

Numeric data

Inexact conversions; it looks like 29, but it's actually 29.0000000000001

 

Date and Time fields

Source uses different algorithm for date and time values than target

Date and Datetime field mismatch

 

 

cbrotz
Pyrite | Level 9

Hi Tom,

 

It sounds like there are issues…..is this a lost cause?  I cannot really see the Paradox data so I cannot compare the output from both but I have had someone else look and the data “appears” to be in the right format.   The field I am trying to filter on should be a 10 character starting with an upper case C.  Even when I use “contain” I can’t even get part of the field to match.

 

ballardw
Super User

1) Your code example did not show any code involving a comparison. No "equal" no "contains".

2) Code is best posted into a code box opened with the forum menu icon {i}. Then it is easy for people to see without having to deal with any of the issues involving opening Docx format files.

 

 

Can you verify that the length is 10 characters? If the SAS length function returns something other than 10 with code like;

 

data test;
   set have (obs=10000); 
   varlength = length(product);
run;

proc freq data=test;
   tables varlength;
run;
TomKari
Onyx | Level 15

Another possibility is an encoding issue...the data is encoded using a different character set than SAS is expecting to see. This is a bit too much for a communities post, but here's a link to the "explanation" sectin in the docn:

 

http://support.sas.com/documentation/cdl/en/nlsref/63072/HTML/default/viewer.htm#p0wqfkbuw8h0qan1hzc...

 

cbrotz
Pyrite | Level 9

Just an update on this situation.....I tried playing around with the size of the field SAS is importing.  It is a 10 digit field.  SAS was reading it in as 11.  Changing to 10 did not bring the data in.  The field is actually blank in the SAS file so that is why "contains" etc. are not working.   There is also an option for "removing characters that might prevent transmission". That did not work either.  I tried increasng the field size -nope.

 

The upshot is that I am putting an extra step in this process to have Paradox output the data into a CSV file.  SAS reads that in just fine. 

 

Thanks for the ideas.  The other idea would be to get rid of Paradox.  🙂

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1745 views
  • 2 likes
  • 4 in conversation