BookmarkSubscribeRSS Feed
GarryL
Calcite | Level 5

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

9 REPLIES 9
Reeza
Super User

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 🙂

 


@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


 

 

art297
Opal | Level 21

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

 

Reeza
Super User

@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...

art297
Opal | Level 21

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

GarryL
Calcite | Level 5

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

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

itchyeyeballs
Pyrite | Level 9

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 

Reeza
Super User

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.

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!

Discussion stats
  • 9 replies
  • 2342 views
  • 0 likes
  • 5 in conversation