SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

DDE commands for Excel

Reply
New Contributor
Posts: 2

DDE commands for Excel

Hi Everybody, 

 

I am looking for the commands (I think they are X4ML) for the DDE process in SAS. 

Is there any documentation that contains this information? There was a link from another thread, but the link went down.

I am particularly looking for:

put '[<what.command()>]'; -----> to put rows 1-20 into a group. In other words, I want all records from row 1-20 in a collapsible group.

 

Thanks, 

 

 

 

Garry

Super User
Posts: 17,780

Re: DDE commands for Excel

[ Edited ]

collapsible groups didn't exist when those commands were written (Excel 4.0) so I doubt you're going to find the command you want. I would probably try some of the VBA commands if you know that syntax. Not sure, haven't tried it, but am definitely curious if you get it to work Smiley Happy

 


GarryL wrote:

Hi Everybody, 

 

I am looking for the commands (I think they are X4ML) for the DDE process in SAS. 

Is there any documentation that contains this information? There was a link from another thread, but the link went down.

I am particularly looking for:

put '[<what.command()>]'; -----> to put rows 1-20 into a group. In other words, I want all records from row 1-20 in a collapsible group.

 

Thanks, 

 

 

 

Garry


 

 

PROC Star
Posts: 7,358

Re: DDE commands for Excel

Do a google search for: dde Koen Vyverman

 

Most things you want to know are somewhere in his papers.

 

You can download the exe file that actually contains all of the documentation at: https://support.microsoft.com/en-us/help/128185/macrofun.exe-file-available-on-online-services

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 17,780

Re: DDE commands for Excel

@art297 those files couldn't be installed on my Windows 7 machine without some additional workarounds this time. Had to switch modes or something.  DDE is definitely going to be done soon...

PROC Star
Posts: 7,358

Re: DDE commands for Excel

Should have been readable by Windows 7 or 8. However, if not (or you're on Window's 10), you can still read it by downloading the reader: https://appuals.com/how-to-read-hlp-files-in-windows-10/

 

Art, CEO, AnalystFinder.com

 

Super User
Super User
Posts: 7,392

Re: DDE commands for Excel

Do be aware that DDE is ancient, MS stopped supporting it well over a decade ago, hence none of the updates since 200x have been implemented.  Its really not recommended to use this any further.  There are manya ways to get data out to Excel if you must use the app, including tagsets - which create XML Excel can read, CSV, libname excel, proc export, dump data to CSV from SAS and use VBA to read it in, SAS Office AddIn etc.  If you want collapsible groups and such very Excel specific things, then you would likely be better off createing a CSV from SAS, then processing the data into a template using VBA.

New Contributor
Posts: 2

Re: DDE commands for Excel

Thanks everybody for the input. It is all great information. What would be the most efficient way to output a SAS dataset into Excel and format it / group the common rows? I'm guessing I would have to use VBA, I'm trying to use as few tools as possible because documentation starts to get really complicated when there are multiple languages involved for a job. 

 

 

Thanks in advanve everybody.

 

 

Garry

 

 

Super User
Super User
Posts: 7,392

Re: DDE commands for Excel

The problem is the use of Group Rollups in Excel, which is competely Excel specific, nothing else uses it.  Therefore, to my mind, the best way to do it is via VBA.  Record a macro of you doing this manually once: View->Macros->Record Macro.  This will give you the code to do this, thn just add a line which opens a CSV, processes the data into the format required and saves out the file.  

Frequent Contributor
Posts: 85

Re: DDE commands for Excel

Assuming you dont have the Office Addin I'd try to do everything natively in Excel, you may be able to skip VBA altogether if you have access to a version of Excel with PowerPivot?

 

You can get SAS ODBC drivers from here - https://support.sas.com/software/products/odbc/

 

You could then use powerquery to set up an automated import into Excel and use Powerpivot to present the data from the data model 

Super User
Posts: 17,780

Re: DDE commands for Excel

Use ODS Excel to create the majority of the file. It can't do the grouping yet but it will do formatting and/or custom highlighting as needed.

Ask a Question
Discussion stats
  • 9 replies
  • 286 views
  • 0 likes
  • 5 in conversation