BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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?

8 REPLIES 8
Tom
Super User Tom
Super User

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.

podarum
Quartz | Level 8

It's Version : 6.1.7601 Service Pack 1 Build 7601

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

podarum
Quartz | Level 8

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

 

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

Tom
Super User Tom
Super User

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

podarum
Quartz | Level 8

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

Reeza
Super User

Try PCFILES instead.

 

libname myfile pcfiles path='path to xlsx';

 

update code goes here.....

 

Ksharp
Super User

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 1959 views
  • 0 likes
  • 5 in conversation