DATA Step, Macro, Functions and more

Updating named ranged cells in Excel using SAS 9.4

Reply
Super Contributor
Posts: 409

Updating named ranged cells in Excel using SAS 9.4

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?

Super User
Super User
Posts: 7,857

Re: Updating named ranged cells in Excel using SAS 9.4

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.

Super Contributor
Posts: 409

Re: Updating named ranged cells in Excel using SAS 9.4

It's Version : 6.1.7601 Service Pack 1 Build 7601

Super User
Super User
Posts: 9,211

Re: Updating named ranged cells in Excel using SAS 9.4

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.

Super Contributor
Posts: 409

Re: Updating named ranged cells in Excel using SAS 9.4

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

 

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

Super User
Super User
Posts: 7,857

Re: Updating named ranged cells in Excel using SAS 9.4

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

Super Contributor
Posts: 409

Re: Updating named ranged cells in Excel using SAS 9.4

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

Super User
Posts: 22,850

Re: Updating named ranged cells in Excel using SAS 9.4

Try PCFILES instead.

 

libname myfile pcfiles path='path to xlsx';

 

update code goes here.....

 

Super User
Posts: 10,618

Re: Updating named ranged cells in Excel using SAS 9.4

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

Ask a Question
Discussion stats
  • 8 replies
  • 227 views
  • 0 likes
  • 5 in conversation