X4ML commands for DDE with MS Excel

[PhilfromGermany]

Hello everyone, I am looking for a list of DDE commands for Microsoft Excel. I searched the Microsoft Download Center and couldn't find anything. I also searched through the forum and only found two posts, of which none mentioned a source or a list of all X4ML commands. Thanks in advance! Phil

[PhilfromGermany]

Hi Reeza, thanks for the response. I should have searched for DDE commands, instead of X4ML 🙂 I installed the macrofun.exe and when trying to open the help file, I get the following error: ------------------- Why can't I get Help from this program? The Help for this program was created in Windows Help format, which depends on a feature that isn't included in this version of Windows. However, you can download a program that will allow you to view Help created in the Windows Help format. For more information, go to the Microsoft Help and Support website. ------------------- I'm using MS Excel 2010. Regards Phil

[RW9]

Hi,

DDE is quite old.  Its not recommended to use it as there is no knowledge of how long it will be supported.  The macrofun is ancient, probably needs WinXp, also you wont get full functionality as Office has advanced over the intervening years - e.g. file format has changed.  What are you trying to do?

Generally speaking in SAS terms Excel is an output format, so the tagset excelxp can be used to produce reports quite simply, and it has style and various options.

If you want to send data, then it would be advisable to use CSV, or something like that - avoid Excel totally.  XML is better, but very verbose.

If you have an Excel book already that you *have* to use use, then look at it from that end, you know the output file, save your data from SAS as CSV, then write a small VBA import program in Excel to read that CSV and process it into the workbook.  This is the easiest way to re-generate graphs/pivots and things in an existing document.  Google VBA read CSV, there are so many VBA examples of pretty much all tasks out there.

The other alternative is to read up on Open Office Format which is what Office apps store their data as now.  The DOCX/XLSX etc. are just zip files containing directories and XML data, so in theory you could read/write Excel files directly using ZIP and text manipulation.  A fair bit of work though.

[jakarman]

Phil, Reeza helped but did you see:

"DDE is basically using really old excel commands that are documented here http://support.microsoft.com/kb/128185  They're excel 4.0 macro functions and NOT SAS functions. "

DDE only works with a SAS/Base on desktop. SAS institute want to move to server-based processing.

DDE was something of the 90'-s Microsoft is still having that run but not really wanting that anymore. Newer options have been implemented.

The help files were compiled versions of html chtml. Microsoft does not want that anymore as of some vulnerabilities. The removed the runtime support in Windows fort that a long time ago. That is your message you are getting. They have still a downloadable one for the ones not in a conversion.

Why are you building something new on outdated tools?

[PhilfromGermany]

What I am trying to accomplish: I have SAS data and I want to dump that data into an existing macro-enabled excel workbook. I then want to run an Excel Macro which assigns the dumped data records to various cells across multiple work sheets within the workbook. The reason I want to use DDE is because: - I can export SAS data into an existing workbook into a blank sheet - I can issue the run macro command - I can issue a Save as... command.

I know that DDE uses Excel 4.0 functions, which are compatible with newer versions of Excel, such as Microsoft Excel 2010. If you know of better alternatives, let me know. Please keep in mind that our platform administrator does not approve of running executables or Perl scripts or python scripts, etc. X commands and signed excel macros are as good as it gets. Jaap, can you provide me with a list of these commands?

[RW9]

As I mentioned, export your data to Excel Sheet, or CSV, then have your macro enabled Excel file load that.  Instead of using SAS to try to push your data into the Excel file, pull it from the file you already have, its very simple:

http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-sheet

There's an example of loading a text file.  Should be very simple to add another macro to the workbook with the load, and then you can also run your other macros (if necessary put them in the on open function so they run automatically.  Will save you many headaches.

[Reeza]

Slide 20 of my presentation earlier this year has most of the code you need:

http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhu...

Here's a link to the Save AS command from SAS Support:

26146 - Write, save, close, open and read Excel files using DDE

Another thing you can do is create named ranges in your Excel Sheet, then use SAS to export to those ranges specifically so you're not copying data around. In my case I created named ranges and then exported to those directly. Links in the workbook were already set up to read from those cells so they would update automatically. The DDE was used to export the different sections to PDF. I was using Windows 7 and Excel 2010 for this.

The final reports, 132 of them, are available online:

Primary health care community profiles � Alberta Health

[Reeza]

Error opening Help in Windows-based programs: "Feature not included" or "Help not supported"

If you have install rights, which I never do, you can try installing the correct application for your software.

[jakarman]

Phil, your position getting some IT support is not ideal.

First the Excel options for ad-hoc manual options.
As you have existing spreadsheets that you want to update and place data somewhere, you can:

- define named ranges in the Excel spreadsheets.  Define named cell references or ranges - Excel (MS site)

- place your data from SAS (SAS-base desktop) into those ranges.

  http://www2.sas.com/proceedings/sugi31/024-31.pdf - SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition

  Your data is placed on the wanted places by you with this. This eliminates the step of a Excel macro doing this.

- Needing am Excel macro to be run (update formulas) you can define that as an autostart excel macro

  Run a macro - Excel   (MS site).  This is what RW9 also proposed to do.
  To start an excel from SAS you may use DDE (exception).

- Be aware it is not very reliable to interact this way with Excel lockings can be a problem.

  As long as you do it manual those issue can be handled by instructions.

More advanced options

Would you have an SAS BI/DI with AMO (Addin Microsoft Office) the integration would be far more easier.

The technical challenge is running that all with a SAS platform admin understanding SAS and BI with a business goal mindset for that part.

With SAS-VA it is all moving more as web-based information.

The technical/governance question.

You are saying you have a platform admin guy. Seen the reaction you got it is either a Windows Unix or DBA educated guy, not somebody with SAS or IT-governance background. I could be wrong. The closed desktop approach,  not allowing to change the desktop installation/configuration and not allowing running some commands and script languages are quite common results as of regualation guidelines (iso27k). For the Analytic and BI this is always getting problematic as the internal detailed technical checklists on what is allowed have not been made with that kind of users. Only the well known operational processes in a strict described way of working have been the model for that.

The effect of this as a of the strict checklists things are forced to do where those guidelines had the intention to prevent that. The result is the opposite of the goal.

You can find those high-level intentions, German-based, at:

- https://www.bsi.bund.de/EN/Publications/BSIStandards/BSIStandards_node.html

- https://www.bsi.bund.de/SharedDocs/Downloads/DE/BSI/Grundschutz/Hilfsmittel/Doku/Vergleich_ISO27001_...  (reference chapters - translation in that PDF).
I suspect when you are asking for the internal IT governance policies they will come up with those as why they are doing it that way.  

[art297]

Phil: Another approach you might want to consider is the exportxl macro (A Poor/Rich SAS Users Proc Export - sasCommunity) I mentioned in another thread yesterday. It uses VB script to accomplish the kind of task you've described, but doesn't require any knowledge of VB script or require that you predefine ranges in Excel. It also can take advantage of Excel templates which might be quite advantageous if you have to repeat this process over time.

[jakarman]

Arthur, I had to think about your approach in the "exportxl" macro. I have remarks for your used namings.

- It is not a poor mans approach it is a smart mans approach.

- It is not just a exportxl function it could be more.

The concept what you have done is translating knowing data-values to a syntax that can be processed and is known on a other platform.

The only disadvantages are:

- the overhead caused by all those additional chars/bytes. For small data no problem.

- there is no immediate connection that can be also an advantage.  For error recognition and recovery some attention 

- Some questions about options that do not exist on both sites. Triggering a macro or other processing could be possible with Excel       

[art297]

: When , Randy Herbison and I originally came up with the concept it was designed primarily as a macro for exporting to Excel using a SAS abbreviation (Note: We weren't able to include Randy as an author because he didn't have enough time to have it approved by his employer).

Prior to publishing the paper we were considering copyrighting the idea, but decided that an open source macro would be far more useful to the community. The "poor" was only intended to convey the fact that one didn't have to buy anything (beyond base SAS) to use it.

After the paper was originally published we continued to refine and expand the macro so that it could: (1) be called on its own; (2) take advantage of Excel templates; (3) write to ranges; and (4) incorporate formats.

I totally agree that the concept could be expanded to include additional functionality and our hope is that others do precisely that and post their enhancements on the macro's sasCommunity.org page (A Poor/Rich SAS Users Proc Export - sasCommunity), and publish some useful papers for the rest of the community.

As for the additional overhead regarding file size, and applicability for only "small" files, you may find that the additional overhead actually speeds up processing for even large (in the Excel sense) files.  Try the following comparison:

data test (drop=i);

  set sashelp.class;

  do i=1 to 50000;

    output;

  end;

run;

proc export data=test outfile="c:\art\textxl1.xlsx" replace

  dbms=excel;

run;

%exportxl(data=test,outfile=c:\art\textxl2.xlsx)

On my computer, running proc export took 1 minute and 3.055 seconds.  Running the macro took 22.403 seconds.

Opening the Excel file produced by proc export took 10 seconds, while opening the Excel file produced by the macro took 8 seconds.

[art297]

: One more thing I just discovered. Users can easily pass formulas to Excel with the macro.  e.g.:

data test;

  format name $10.;

  length name $10;

  label name='Name';

  set sashelp.class;

  formulas=catt('=d',_n_+1,'+e',_n_+1);

run;

%exportxl(data=test,outfile=c:\xltest.xlsx)

[jakarman]

Arthur, yes you are using the interface Excel understands you can use all specifics of Excel. Formulas should be as all others as long as VB is supporting that. That is what I made the remark on as "to be liked". There are possibilities to integration to office/excel yet to be unknown.

Patenting this concept will be difficult I think (or may be not). SAS is doing some similar trick when getting data from Excel using Eguide. The convert it as a SAS-code with all datalines (coming from Excel) and upload that so it can be run. Not it is not well documented, a technical guy can get the log in case of problems and is seeing what is happening, Normal users are told the excel is read and the data is run/processed at the server side without any additional information.

I tried you test program at my computer proc export is running in 20,43 second (UE). I have to download you macro and try that also. The processing time was not my intention but the sizing of the intermediate file. I have seen comments on the sizing of ODS tagsetsXP as having to crete too big datasets. My view classification on big/small is that everything that fits with Excel is small. 950000 records was the daily number of smf records I did See in the 90's tracing every enter in a pseudo interactive system. Not a too big SMB company about 1000 office users. k 

For the speed I think SAS could improve some processes procs. I know you agree on that with transpose.