SAS to Excel DDE produces test but not output data

I've been following documentation online and browsing other stack overflow queries but I have not yet been able to find a way to output my SAS dataset to excel via DDE.

The version of SAS I am running is SAS9.4 The version of excel I am running is microsoft office 2016 - excel 2016

The code I use to export is

/*Excel DDE interface options*/ /*TEST*/
options noxwait noxsync;

X '"C:\Users\user.name\Desktop\template_dde.xlsx"';

data _null_;
    rc=sleep(15);
run;

filename ddedata dde 'excel|SFA!r2c1:r4000c56';

data _null_;
    file ddedata notab;
    set work.Results_output_format end=eof;
put '"THIS IS A TEST"';
run;

%LET timestamp = %SYSFUNC(PUTN(%SYSFUNC(DATE()),yymmddn8.));
%LET hourstamp = %SYSFUNC(COMPRESS(%SYSFUNC(TIME(),time.),%STR( :)));

data _null_;
    length cmnd $150.;
    file ddedata;

    cmnd = '"[save.as("C:\Users\user.name\Desktop\&timestamp._&hourstamp._template_dde.xlsx")]"';
    put cmnd;
    put '[quit()]';
run;

It outputs the "this is a test" and then outputs the save statement but my data is not exported and the file is not actually saved.

Am I overlooking anything?

Solution

SET does not implicitly place the data set contents in the Excel file. You need to use a PUT statement to add data to the worksheet. You also need to use a separate file name to send commands to the Excel|system channel.

Your code had single quotes around an attempted macro variable resolution -- that was incorrect.

This code presumes there is an existing workbook c:\temp\template_dde.xlsx

* open template in Excel;
X '"C:\Temp\template_dde.xlsx"';

* wait for app to start and file to load;
data _null_; rc=sleep(3); run;

* define filerefs for data transfer and command execution;
filename ddedata dde 'excel|Sheet1!r2c1:r4000c56';
filename ddecmnd dde 'excel|System';

* pump data into excel cells at location specified in ddedata;
data _null_;
  file ddedata ; * <--- removed your NOTAB option, so now I dont have to put '09x' between each variable;

  set sashelp.class;
  put name sex age height weight; 
run;

* Extended ISO timestamp as yyyy-mm-dd_hh-mm-ss;
%let timestamp = %sysfunc(translate(%sysfunc(datetime(),E8601DT),%str(_-),%str(T:)));

* send commands to save workbook and close Excel;
data _null_;
    file ddecmnd;

    put "[save.as(""C:\Temp\&timestamp._template_dde.xlsx"")]";
    put '[quit()]';
run;

Plenty of conference papers on DDE, such as "SAS®-with-Excel Application Development: Tools and Techniques", SUGI 31, LeRoy Bessler, Assurant Health