0

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?

  • To write to a template I prefer to have a named range in the template and then you can proc export directly to that named range in the Excel file. FCOPY() will copy a template file to create a new version, then I also avoid the save as step. http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhurshed-CoordinateComplexReports-Spring2014.pdf – Reeza Oct 31 '18 at 17:25
  • DDE isn't really a good idea for a new process, the only reason I still use it, is when I need to call a macro from the Excel file, usually to convert to PDF. – Reeza Oct 31 '18 at 17:27
  • Thanks! I'll read through this –  Oct 31 '18 at 17:38

1 Answers1

2

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

Richard
  • 25,390
  • 3
  • 25
  • 38
  • I am surprised that you don't need to have `dsd dlm='09'x` option on FILE statement to have the PUT statement add the tabs between the fields. – Tom Oct 31 '18 at 12:59
  • The DDE fileref engine will implicitly add '09'x separator. There is an awkward discussion in the documentation [Using the NOTAB Option with DDE](https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=hostwin&docsetTarget=n1aqiv6biqkjbnn1gu1388hp7aab.htm&locale=en#n01w21u2831k82n1flmtzfdwj1dl) – Richard Oct 31 '18 at 13:17
  • But is that just because the person that wrote the DDE documentation doesn't understand how the DSD option on the FILE statement works? – Tom Oct 31 '18 at 13:38
  • This works but now I am going to have to do a bit of research about just simply outputting column to column as the spaces in character strings outputs to different columns, thanks for the help –  Oct 31 '18 at 14:41
  • 1
    Try reverting back to `NOTABS` and using explicit `'09'x` separators in the `PUT`, or `NOTABS` and the filename option `DSD DLM='09'x` – Richard Oct 31 '18 at 15:32