Deleting rows in Excel with SAS

[dtchoi86]

I need help delete rowings in an excel table using SAS DDE.

I have this code so far that can select the rows I'm interested in deleting:

filename cmds dde 'excel|system';
data _null_;
  file cmds;
  put '[workbook.activate("Sheet1")]';
put '[select("R2:R4")]';
run;

I cannot figure out the code to delete the rows selected.

If the starting sheet1 looks like this:

1

2

3

4

5

6

End would be:

1

5

6

Thank you for all the help!

[ChrisHemedinger]

This paper provides a nice reference of DDE commands you can execute from SAS.  I think the one you want is [edit.delete(3)], which is "delete entire row". 

You probably know this already, but DDE will work only when PC SAS and Excel are on the same machine.  If your process will ever evolve to run on a remote server (or on a non-Windows version of SAS), you'll need another approach to manipulate Excel content with SAS.

[RW9]

Hi,

@ChrisHemedinger has posted some nice information on DDE.  However I would like to take a slightly different tack.  My question would concern the why of the matter.  Let me explain.  DDE is very old technology, and is only supported in legacy mode.  It lacks a lot of the functionlity which has been built into the Office Suite over the last 15 years or so.  So from that perspective I would actively advise against using it, its old and less functional and may not be supported going forward - and in some cirumstances does not work at all - note the mention of things needing to be on the same machine and have certain licences.  

Secondly, Excel itself is not a: database, data capture utility, data transfer format etc.  It has no structure.  Hence it is quite dangerous to assume that a program written to remove certain rows, is still valid or can be validated on subsequent files - i.e. you could end up removing data you didn't want to or corrupting other linked sheets/graphs etc.

Thirdly, why do you want to do this in the first place.  Excel is a relatively simple tool, installed on most machines.  Why would you need to write a program in a completely different software application to make changes to a file from that, which would be an extremely simple task in Excel?  If you need to automate it, write (or record) a simple VBA macro in Excel.  There seems to me no reason to go through the process or documenting, writing, and testing a SAS program to do something which is a key click in Excel?

[LinusH]

Strongly agree with @RW9.

If you still wish pursue with Excel file updates, use the Libname Excel engine. The you can modify your Excel data using a data step modify, as an example.

If you can't determine which rows to delete by using relational operations, you are definitely using the wrong tool.

What kind of data do you have in your Excel?

If that some kind o f report, consider to move all data management into SAS data sets, and then distribute the reports from SAS, using ODS, tagstes, SAS Add-in 4 MS Office etc.

[ChrisHemedinger]

I agree with @RW9 and @LinusH.  DDE is a form of technical debt for SAS shops, and I've written about the topic here:

Why your DDE programs don't work anymore

Still, I try to not judge too harshly.  I recognize there is a lot of DDE legacy out there, and I'm often meeting with customers who are saddled with it and looking for alternatives.  My main point: don't build new processes that depend on DDE.  You can keep your existing DDE processes running as long as your SAS environment isn't rearchitected.  And when that happens, there are many viable alternatives that use newer capabilities of SAS working with Excel.  The options you choose depend on how you're using DDE, and one single approach might not satisfy all varieties of your use cases.

[lrozzelle]

Sorry, but this comment, as well as all the other anti-DDE comments simply aren't true.  There is no alternative to building Excel reports that has the functionality of DDE.  All the ODS destinations for Excel are extremely limited and only useful for basic Excel reports.

Automation is a thing.  Another user commented that it didn't make sense to write code to delete rows when you can just manually do it with your mouse in Excel.  I guess he's never had to automate a reporting process.  Personally, automation is all I do these days.

With DDE, you can have a VERY detailed Excel template which has formatting details well beyond anything SAS can do natively, then open the template in SAS and drop data into exact rows/cells and it works beautifully.

There is no replacement for DDE in SAS, not even close.

[AhmedAl_Attar]

I'm surprised no one had responded with information about the ODS Object Writer at the time, when It had been released since 2012!?

Here are just few examples that could challenge the DDE usage and dependency

• https://support.sas.com/resources/papers/proceedings12/071-2012.pdf
• https://www.lexjansen.com/sesug/2016/RV-235_Final_PDF.pdf
• https://support.sas.com/resources/papers/proceedings16/2240-2016.pdf

[vfarmak]

Hi @ChrisHemedinger 

I know that this is a very old post.

I did not know the DDE commands until I found this post!

Do you have any other alternatives other than DDE?

I am using SAS Data Integration Studio 9.4

Best Regards,

Vasilios

[SASKiwi]

DDE only works if you have PC-installed SAS and in any case is deprecated Excel functionality.

The best advice I can give is to maintain a copy of your Excel data in a SAS dataset. Then you can just delete the rows in the SAS copy then do a complete re-export.

[AhmedAl_Attar]

Hi @vfarmak 

Here is a replacement to DDE that would run on Linux & Wndows Platfroms even if you don't have Excel installed.

Have it Your Way: Using the ODS EXCEL Destination with the DATA step Report Writing Interface

Hope this helps

[RW9]

Could you confirm the functionality of this DDE replacement?  From what I see it is only for creating Excel files, much like the rest of the ODS system.  It doesn't for instance, have the ability to go into existing Excel files and make alterations, at least from what I could see.

My original post still stands, Excel has its pluses, its a great frontend for data entry and such like, but it is not a good backend.  If you have to work with Excel files, then its perhaps a good idea to invest some time in learning Open Office, an dperhaps other tools like C# or python which can alter directly existing files.  ODS Excel is good for writing out data from SAS to Excel, but thats really about it. 

I would really consider what the purpose is, look at data, data entry, and reporting as different elements as there a many ways of doing each and not necessarily one which is great at all of them. 

[ballardw]

---

@RW9 wrote:

Could you confirm the functionality of this DDE replacement?  From what I see it is only for creating Excel files, much like the rest of the ODS system.  It doesn't for instance, have the ability to go into existing Excel files and make alterations, at least from what I could see.

You might specifically describe exactly which functionality you are discussing, if nothing else so we don't have to determine which of the old posts you are referencing.

ODS Excel will not modify existing Excel files, so your understanding is correct there.

HOWEVER, not is DDE not recommended it very well may not work even when you meet the requirements of the Excel installation. The communications channels used by DDE have been pre-empted by some other software. I had exactly one program that used DDE with Excel that stopped working. The problem was traced to Cisco Jabber as that was interfering. The process, not the active program the service, had to be killed using the Windows Task Manager or similar to allow DDE to function. You may not be able to kill other programs required by your organization just to allow no longer supported software to run. Additionally some of the command string options depend on the specific version of Office products installed. So you may have fun tracing those down when your IT department updates your Office.

---

My original post still stands, Excel has its pluses, its a great frontend for data entry and such like, but it is not a good backend. 

---

I have cleaned up so much garbage entered in Excel that I consider it marginal at best for data entry or a front end. Type 5-6 into a cell. See what is displayed. If the cell was 'general' formatted the value you are likely to see is 6-May and now has numeric value of 45052 (if entered in 2023). People that don't catch this kind of behavior end up with values that are extremely hard to get back to what was intended. People routinely use the drag operation to copy similar values and pay not attention to the results. I had a project where someone doing that managed to create over 100 school districts instead of having 100 records associated with one school district. Columns that change from text to number with the numbers formatted differently for part of the column (birth dates with currency formatting, identification numbers with date formatting).

Other spreadsheets have similar if not identical issues for data entry unless the people are extremely well trained and motivated to pay attention to details.

I might submit that needed manual changes is an indication that the original approach was flawed.

[RW9]

The functionality we are discussing is in the Subject of the question, "Deleting rows in Excel", which is why I suggest ods is not useful.

I have never, ever recommended to use DDE?

Excel's front end far exceeds most other fronts ends (Forms, text, etc.), drag and drop, formula fill, filtering, functions etc. make it very quick and easy.  This of course comes at the cost of anyone who uses anything other than the front end.  Hence why I don't recommend to use it as data storage.

[SASKiwi]

ODS can't modify existing Excel spreadsheets so is a non-starter for the original use case.

It would be an option if a SAS dataset was the original version of the Excel table and ALL data maintenance was done in SAS, with the Excel spreadsheet just being a copy of the original.

ODS then is just acting as a copy process that creates a new workbook with the required data each time it is run.