topic Re: Updating named ranged cells in Excel using SAS 9.4 in SAS Programming

Updating named ranged cells in Excel using SAS 9.4

podarum — Tue, 28 Nov 2017 15:21:40 GMT

In SAS 9.4, I’m trying to use LIBNAME XLSX to read and write to Excel files. I tried to update a named range using SAS 9.4, but it doesn’t work. One of the blog (link provided below) says that “the XLSX engine does not support Excel named ranges”

https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

In SAS 9.3, I was able to update named range cells using the below code. I used “excel” engine to create a libref “AFIL” using LIBNAME statement. “Table_NamedRange” is a named range in the excel spreadsheet located in - "V:\Test\ABC.xlsx”. I was able to update the named range cells in the excel sheet with the data from the SAS table “work.AFILData” (code provided below).

Below is the code I used in SAS 9.3, is there an equivalent code for 9.4?

libname AFIL excel "V:\Test\ABC.xlsx”;

data AFIL.Table_NamedRange ;

set work.AFILData;

Run;

How can I update a named Range cell in excel using SAS 9.4?

Re: Updating named ranged cells in Excel using SAS 9.4

Tom — Tue, 28 Nov 2017 15:28:04 GMT

It will help others to help you if you include the exact version of SAS, including maintenance release.

- Latest version as of Nov2017 is 9.4M5 .

Also operating system version and the release/version of EXCEL you are using.

Re: Updating named ranged cells in Excel using SAS 9.4

RW9 — Tue, 28 Nov 2017 15:29:17 GMT

As far as I am aware they have not removed the libname excel engine from 9.4, so you can still use your original code.

Re: Updating named ranged cells in Excel using SAS 9.4

podarum — Tue, 28 Nov 2017 15:38:36 GMT

It's Version : 6.1.7601 Service Pack 1 Build 7601

Re: Updating named ranged cells in Excel using SAS 9.4

podarum — Tue, 28 Nov 2017 15:40:15 GMT

This is the error I'm getting when executing the libname statement

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

Re: Updating named ranged cells in Excel using SAS 9.4

Tom — Tue, 28 Nov 2017 16:26:34 GMT

Usually that Class not registered error message is an indication that your SAS and OFFICE installations are using different numbers of bits (32 vs 64). If you cannot update your OFFICE installation to match your SAS installation then you will need to use the SAS PC Files Server to make the connection between SAS and EXCEL.

http://support.sas.com/kb/54/413.html

Re: Updating named ranged cells in Excel using SAS 9.4

podarum — Tue, 28 Nov 2017 16:32:42 GMT

In 9.3 I was using 64-bit and Excel was 32-bit, same as it is now.. when it worked

Re: Updating named ranged cells in Excel using SAS 9.4

Reeza — Tue, 28 Nov 2017 16:48:55 GMT

Try PCFILES instead.

libname myfile pcfiles path='path to xlsx';

update code goes here.....

Re: Updating named ranged cells in Excel using SAS 9.4

Ksharp — Wed, 29 Nov 2017 13:58:20 GMT

Can you use PROC IMPORT+noreplace + RANGE=' ' option to update ?