BookmarkSubscribeRSS Feed
tommyschyman
Calcite | Level 5

Hi,

I'm having trouble to update an existing sheet in an Excel workbook. I'm using the following code:

libname xls excel "file.xlsx" scan_text=no;

proc datasets lib = xls nolist;

     delete 'ValueLevel$'n 'WhereClauses$'n;

run;

quit;

data xls.'ValueLevel$'N(dblabel=yes);

   set to_vallev;

run;

data xls.'WhereClauses$'N(dblabel=yes);

   set to_whecla;

run;

libname xls clear;

When using this code, the following error occurs:

ERROR: The MS Excel table ValueLevel$ has been opened for OUTPUT. This table already exists, or there is a name conflict

       with an existing object. This table will not be replaced. This engine does not support the REPLACE option.

ERROR: The MS Excel table WhereClauses$ has been opened for OUTPUT. This table already exists, or there is a name conflict

       with an existing object. This table will not be replaced. This engine does not support the REPLACE option.

Is there anyone who has an idea on how to update the already existing Excel sheet?

Regards,

Tommy

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Yes, my suggestion would be to export your data to CSV, then in your Excel file some write a VBA script to open the exported data and update your existing WB.  The simple fact is that whilst SAS supports writing out to Excel files via various methods, those methods are output prodedures.  I.e. they are meant to produce a report.  Excel is not a database (or anything else for that matter), so to force it to do things which you would expect in software built to do the task, you need to force it, or use the tools within itself. 

Reeza
Super User

Hmm...that's usually the method I use, first drop the table and then recreate it and it works. The only difference is that I use proc sql to drop the tables instead of proc datasets. Is your proc datasets working and is your excel file closed?

tommyschyman
Calcite | Level 5

The excel file is closed and the proc datasets seems to work:

557   proc datasets lib = xls nolist;

558        delete 'ValueLevel$'n 'ValueLevel$_xlnm#_FilterDatabase'n 'WhereClauses$'n;

559   run;

NOTE: Deleting XLS.'ValueLevel$'n (memtype=DATA).

NOTE: Deleting XLS.'ValueLevel$_xlnm#_FilterDatabase'n (memtype=DATA).

NOTE: Deleting XLS.'WhereClauses$'n (memtype=DATA).

560   quit;

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.01 seconds

      cpu time            0.03 seconds

Have tried to use proc sql but get the same result. When opening the excel file after libname clear the deleted sheets are empty on data. I can't figure out why it's not working.

Reeza
Super User

Sometimes you have a named range and a sheet with the same name, what does your library look like when you navigate to it in the SAS explorer window?

tommyschyman
Calcite | Level 5

There are no ranges in the excel sheet, so in the SAS explorer there are only the sheet names with the $ as suffix. The actual excel sheets are using the filter function, and that is not deleted when deleting the sheets using proc datasets or proc sql. Can that be an issue?

Reeza
Super User

Try dropping both and see if it works.

tommyschyman
Calcite | Level 5

I removed the filter function in the actual Excel sheets, but it will not work... As I said there are no ranges, so the only thing that can be deleted are the two sheets I want to update. There is no problem to create a new sheet in the Excel workbook, so there must be something in the actual sheets that blocks this update.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

It could be your hitting one of those "features" of Excel.  Could be hidden items, formatting, special characters.  It is the problem with using Excel I am afraid, its not in any sense structured or controlled, so your problem could be quite an array of issues.  Try highlighting the cells on one sheet in Excel, and then on the Home tab select the drop down arrow next to Clear.   Select each item in turn and see if anything changes on the sheet.  Also check you have no special characters anywhere.  Also check that all the items on the sheet are readable by SAS, ie, look at the dataset in the libname you create.  Any hidden rows/columns etc.  Or save yourself a headache and stop using Excel Smiley Happy

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
  • 2624 views
  • 0 likes
  • 3 in conversation