DATA Step, Macro, Functions and more

Put SAS Data Into Specific Cells Without Using DDE

Reply
New User
Posts: 1

Put SAS Data Into Specific Cells Without Using DDE

Hello guys,

Does anyone know how to put SAS data (like two numbers) into specific cells (for example, Q5:Q6) in Excel sheet without using dde command because it does not work in Enterprise Guide. This is for reporting purpose and I want to automate the report generating process. I know cope and paste would work, but I just want to see if I can fully utilize the functions in SAS.

I know you can create a excell libname reference, like:

libname something EXCEL "location/filename.xlsx";

And it can create a new Excel sheet or create a name-range for the data, but I haven't found ways to put data into specific cells using this method.

Thank you,

Mandy

Super User
Posts: 9,691

Re: Put SAS Data Into Specific Cells Without Using DDE

If I was right, you could put data into specific cells.

libname something EXCEL "location/filename.xlsx" scantext=no ;

Super User
Super User
Posts: 7,421

Re: Put SAS Data Into Specific Cells Without Using DDE

TBH your not generating a report from SAS, just doing a couple of numbers.  The report is already there is you just want to copy the two numbers over.  Easiest way is just to copy and paste.  You could export the two values and have Excel load the file and place the values where you like using a VBA macro, but for the sake of two numbers is it worth it?

Maybe also think about generating your whole report from SAS (preferably not using Excel at all).

New Contributor
Posts: 2

Re: Put SAS Data Into Specific Cells Without Using DDE

Hi Mandy,

 

Did you ever figure-out a way to populate specific cells in an excel file using SAS/EG?

 

I receive 100 monthly excel files already pre-populated with data, and I need to add 5 numbers in specific locations in the existing files. DDE would do the trick but don't have it, copy/paste is not efficient.

 

If I could only figure-out how to push data to the specific cells in the excel files using SAS/EG,...,

 

Will keep working at it but if you found a solution, would be great to hear from you.

 

Thanks,

Bart

New Contributor
Posts: 2

Re: Put SAS Data Into Specific Cells Without Using DDE

I'm switching from using SAS on my local PC running Windows 7 to using SAS enterprise guide locally with SAS grid installed on a remote server running Linux so will be losing DDE support.

 

I am investigating replacing DDE functionality by calling R via SAS' system command and using functions in the xlConnect package. The R xlConnect package appears to allow users to specify where to place data in an excel spreadsheet; as well as providing lots of other controls on formatting in Excel and such.

 

I currently have a set of SAS macros that I use to invoke DDE and the idea is to replace the guts of the SAS macros with R functions (maybe submitted via IML) that replace the DDE functionality. This means that I won't have to modify my SAS programs substantially to work with SAS Enteprise Guide and SAS grid.

 

There are benefits to this:

-Hundreds of legacy programs don't need to be re-written

-Don't need to go and learn VBA ( I personally think R is more accessible)

-Don't need to invert the relationship between SAS and Excel for producing tables: SAS pushing data to Excel versus Excel pulling data from SAS

-SAS logs for QC/auditing purposes

 

Anyway, I'm still working through this and will be glad to share my findings at the end.

 

Ask a Question
Discussion stats
  • 4 replies
  • 326 views
  • 0 likes
  • 5 in conversation