SAS 9.1.3 export / import error with Excel 2016

[Pale]

A client is unable to export and import .xls (Excel 97-03) files with the wizard to and from an old version of SAS software. Updating the software is possible, but in our current situation we prefer the least troublesome solution. How should we proceed?

A connection error message pops up during the wizard and the log says the following (roughly translate):

"Connection: Unexpected error in the external database driver / controller"

"Error in the LIBNAME statement"

The libname statements are made in this manner:

LIBNAME name 'path';

If an export was attempted, a ~6kb corrupted .xls file would appear.

Other necessary info

Windows 7 Pro 32bit 7601

SAS 9.1.3 SP4 XP_PRO platform

Excel 2016 32bit

[ballardw]

Save the Excel as CSV and import that.

Use a large value of Guessingrows to have better data import results.

Without knowing how the "export" was attempted suggestions may be problematic. Could actually be data content or something else.

[Pale]

Exporting in CSV worked, but Excel was unable to match the commas as columns so the file ended up being only a big mess of numbers. After that I tested Excel 5 -95 and Excel 4 .xls formats and everything worked fine. Converting files is likely going to be an annoyance for the client, but still better than nothing.

The data is positive and negative numbers with decimals and some text as headers. No functions, formulas or anything of the sort. I might be able to show an example table after consulting with the client, if it is truly needed.

The attached picture is pretty much the whole export process. After the path for the workbook is chosen SAS throws an error message ''Connection Error, see log for details'' once OK button is pressed.

[Reeza]

Upgrade anyways, 9.1.3 is ancient, and you'll get a lot of benefits from migrating to 9.4 TS1M5. 

[Pale]

Is one of those benefits better integration with Microsoft Office? What functionality might break after the update is done? Are the old SAS files going to work without a hitch?

Our clients policy is pretty much ''If it ain't broke, don't fix it'' and would prefer to update only if it's necessary.

[Patrick]

@Pale

SAS code is highly backward and forward compatible.

SAS9.1.3 is on support level C. http://support.sas.com/techsup/support.html 

SAS9.1.3 is more than 10 years old!!! https://blogs.sas.com/content/iml/2013/08/02/how-old-is-your-version-of-sas-release-dates-for-sas-so...

If you wait for too long then there is a chance that there won't be a direct migration path from SAS9.1.3 to the latest SAS version (=one has to go via an intermediary SAS version). That's already true for some SAS9.1.3 components (i.e. SAS DI Studio jobs).

http://support.sas.com/rnd/migration/utility/upgrade.html 

How simple migration in your environment is, depends on what you've licensed and what you're doing with it. Given that you're still happy with SAS9.1.3 I'd assume your SAS usage is rather on the "SAS Foundation/coding only" side.

There has been a lot of improvement since SAS 9.1.3 and as upgrades don't incur additional license costs you simply don't get the full value for your buck if you don't upgrade.

As for Office integration: Yes, much better, i.e. the XLSX engine and ODS EXCEL

[Reeza]

There's no software expense to upgrading, but there's a ton of new features including way better graphics and ODS Excel, much more custom formatted reports. 9.1.3 is going to lose support soon anyways and expecting a 14 year old software to work with technology that's less than a year old (MS2016) is not a reasonable expectation.

Your installation is a unruly teenager about to leave home...