DATA Step, Macro, Functions and more

Deleting rows in Excel with SAS

Reply
Contributor
Posts: 29

Deleting rows in Excel with SAS

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!

Community Manager
Posts: 2,761

Re: Deleting rows in Excel with SAS

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.

Super User
Super User
Posts: 7,401

Re: Deleting rows in Excel with SAS

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?

Super User
Posts: 5,256

Re: Deleting rows in Excel with SAS

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.

Data never sleeps
Community Manager
Posts: 2,761

Re: Deleting rows in Excel with SAS

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.

Ask a Question
Discussion stats
  • 4 replies
  • 529 views
  • 8 likes
  • 4 in conversation