topic experience working odbc library for dbf sources in SAS Enterprise Guide

experience working odbc library for dbf sources

SAS_LuisBolivar — Sat, 30 Oct 2021 02:33:16 GMT

Greetings community.

I have a case where it is necessary to generate and read dbf files from SAS Enterprise Guide. Due to not having the SAS component Access to pc file licensed, I must look for an alternative.

In the licensed components there is the SAS connect to odbc so I have thought about solving the situation by configuring a library of this type.

I had not worked a similar case, whenever I have worked with dbf files I have used the proc export.

I would like to know if any expert colleague from the community has worked on these files via odbc library.

Thanks.

Re: experience working odbc library for dbf sources

SASKiwi — Sat, 30 Oct 2021 03:00:27 GMT

What software is being using on the DBF side? I did see this SAS Note which may be helpful. Apart from SAS/ACCESS to ODBC, you also need a compatible ODBC driver installed on the computer where your SAS session is running. If its your PC you can install that yourself. If you use a remote SAS server, then it's a job for your SAS administrator.

Re: experience working odbc library for dbf sources

AlanC — Sat, 30 Oct 2021 03:50:21 GMT

Wow. Haven't seen a dbf file in decades. Why not use Excel's ability to read it and then convert to something else? MS also still has FoxPro drivers available. See here: Visual FoxPro Downloads | Microsoft Docs. You can also do a OleDb to ODBC conversion. Also, look here for connection strings and an ODBC driver: DBF / FoxPro connection strings - ConnectionStrings.com

Re: experience working odbc library for dbf sources

SAS_LuisBolivar — Sat, 30 Oct 2021 13:27:33 GMT

Good Morning.

Thanks for the comments from him.

What happens in this case.

The client was working with a very old version of SAS, with the version of SAS Enterprise Guide they were using it was possible to export to this file.

When performing the new installation of SAS 9.4 M7, the SAS Enterprise Guide version does not have this format available within the Step Export.

We have offered the option to append the SAS Access to PC File component to the license, but due to budget issues, it is currently not possible to append the component.

Reading a little SAS documentation I found this information:

"Open database connectivity (ODBC) standards provide a common interface to a variety of databases, including AS / 400, dBASE, Microsoft Access, Oracle, Paradox, and Microsoft SQL Server databases"

In my case, I have never had the need to establish a library of this type of DBF file, so I see the need to consult the community.

I did some tests in a non-productive environment, performing the following steps:

1. the driver download from the following link: https://www.microsoft.com/en-us/download/details.aspx?id=54920

2. configure the odbc at the operating system level

3. work with the library, using querys en guide.

4. I generate the dbf by query instead of an export

It is a solution that does not give the user total autonomy, since the user will need the data and the path established in the library

configuration, but I understand this solution can solve the situation we are presenting. It's just generating two dbf's.

I would like your comments on what I am raising.

I understand that the correct solution is to append the SAS Access to PC Files component.

Thanks.

Re: experience working odbc library for dbf sources

SASKiwi — Sun, 31 Oct 2021 02:21:14 GMT

Your approach looks good to me. I'm surprised anyone still uses DBF as its such a old format. It originated way back in the early days of PCs in a product called dBase.

Re: experience working odbc library for dbf sources

AlanC — Sun, 31 Oct 2021 03:03:53 GMT

They can also do the allocation using the following syntax (adapt to your needs). I prefer this so you don't have to set up the connection in the ODBC sources:

libname mylib odbc noprompt="driver=SQL Server Native Client 10.0;server=server-name;uid=user-ID;pwd=password; database=SQL-database" schema=schema-name;

See the link I put in my other answer and get the dBase connection string from the connection strings website (it is the best place for all connection strings).

Re: experience working odbc library for dbf sources

SASKiwi — Sun, 31 Oct 2021 03:40:20 GMT

@AlanC - Agreed. We use exactly the same approach with defining everything in the connection string, and the connection string website you've provided is one we use a lot too.