DDE output to specific rows/columns in specific Excel file

[BigPete]

Why does it tell me "ERROR: Physical file does not exist, EXCEL|C:\Users\peter.ahn\Desktop\vol70_test\Output\tab002.xlsx\Table 1!R5C1:R101C5." even though the Excel file DOES exist in my target folder? It was working fine on another table using same syntaxes and commands. (The only difference in that was using 001 instead of 002.) I'm trying to spit out my final dataset into a specific range of rows and columns of my target Excel file.

proc sql;
Create table table002 as select distinct
  roworder, _&py, _&cy, _&ny, pchange
from table002_roworder a left join pre_fin b on a.varname=b.varname
order by roworder;
quit;


proc contents data=table002(drop=roworder)	out=varlist noprint;
proc sort; by varnum; run;
%array(cord,data=varlist,var=name)

data _null_;
  set table002;
  call symputx('nrows',_n_);
run;

%let outfile=C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx;

OPTIONS NOXWAIT NOXSYNC MISSING=' ';
x '"C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx"';
/*To let the file to open allow SAS to sleep for 5 seconds*/
data _null_;
   x=sleep(5);
run;

%let firstrow=6;
%let firstcol=7;

%let lastrow=%eval(&firstrow+&nrows-1);
%let lastcol=%eval(&firstcol+&cordn-1);

filename t002 dde "EXCEL|&outfile\Table 1!R&firstrow.C&firstcol.:R&lastrow.C&lastcol." notab;

data _null_;
set table002;
file t002;
put %do_over(cord,between=%str('09'x),phrase=?);
run;

29         proc sql;
30         Create table table002 as select distinct
31           roworder, _&py, _&cy, _&ny, pchange
32         from table002_roworder a left join pre_fin b on a.varname=b.varname
33         order by roworder;
NOTE: Table WORK.TABLE002 created, with 97 rows and 5 columns.

34         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

35         
36         
37         proc contents data=table002(drop=roworder)	out=varlist noprint;

NOTE: The data set WORK.VARLIST has 4 observations and 41 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

38         proc sort; by varnum; run;

NOTE: There were 4 observations read from the data set WORK.VARLIST.
NOTE: The data set WORK.VARLIST has 4 observations and 41 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
2                                                          The SAS System                            16:35 Saturday, October 1, 2022

      cpu time            0.00 seconds
      

39         %array(cord,data=varlist,var=name)

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
NOTE: There were 4 observations read from the data set WORK.VARLIST.

NOTE: CALL EXECUTE routine executed successfully, but no SAS statements were generated.
40         
41         data _null_;
42           set table002;
43           call symputx('nrows',_n_);
44         run;

NOTE: There were 97 observations read from the data set WORK.TABLE002.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

45         
46         %let outfile=C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx;
47         
48         OPTIONS NOXWAIT NOXSYNC MISSING=' ';
49         x '"C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx"'
49       !                                                               ;
50         /*To let the file to open allow SAS to sleep for 5 seconds*/
51         data _null_;
52            x=sleep(5);
53         run;

NOTE: DATA statement used (Total process time):
      real time           5.00 seconds
      cpu time            0.00 seconds
      

54         
55         %let firstrow=6;
56         %let firstcol=7;
57         
58         %let lastrow=%eval(&firstrow+&nrows-1);
59         %let lastcol=%eval(&firstcol+&cordn-1);
60         
61         filename t002 dde "EXCEL|&outfile\Table 1!R&firstrow.C&firstcol.:R&lastrow.C&lastcol." notab;
62         
63         data _null_;
64         set table002;
65         file t002;
66         put %do_over(cord,between=%str('09'x),phrase=?);
67         run;

ERROR: Physical file does not exist, EXCEL|C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx\Table 1!R6C7:R102C10.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):

[Patrick]

One reason could be that you've got the target Excel file open. 

A more important comment is: Don't use DDE. Not only is it really slow it's also totally outdated. https://blogs.sas.com/content/sasdummy/2014/10/14/dde-is-doa/ 

There are other ways to write data to Excel not starting with cell A1. It's may-be worth you familiarize yourself with ODS Excel. https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/p09n5pw9ol0897n1qe04zeur27rv.htm 

[BigPete]

Thanks for the reference and all. Any idea how I can get ODS EXCEL to paste to J5 without overwriting original contents?

ods excel file="C:\Users\peter\Desktop\vol70_test\Output\tab001_test.xlsx"	options(start_at="J5");
proc print data=table001 noobs; run;
ods excel close;

This is the pre-existing file I'm trying to paste extra data to let's say starting at J5.

I saved a duplicate copy as "table001_test" just in case and attempted ODS EXCEL on that. This is what came out...

Not exactly the result I wanted. What ODS EXCEL options can help preserve original contents?

[Patrick]

To my knowledge there isn't any way with SAS to update an existing Excel sheet. But you can always first read the whole sheet into SAS and then fully re-create it with the added data. 

Another option is to use Python (SAS embraced 3rd party software). Python package openpyxl allows to update cells within an existing sheet.

[SASKiwi]

Unfortunately you can't use ODS for adding to existing spreadsheets. ODS can write spreadsheets from scratch but not interpret existing ones. You can use PROC EXPORT to write a new or replace an existing sheet. You could use an Excel macro to copy it where you want.

[Kurt_Bremser]

Don't use Excel for business intelligence. You have the 500 ton King Kong if BI (SAS) at hand, so why rely on a toy?

Keep all data and action in SAS, and create only the final report as a whole in whatever form needed.

Personally, I like to create complex reports in HTML form served from the web server already supplied with SAS.

[BigPete]

Ah I now realize that I forgot to change 'Table 1' to 'Table 2' in my FILENAME statement since the worksheet's name is the latter in my target Excel file. Now it's fixed. Here's another issue spinning off:

When I try to plug in macros to attempt running the code with

x '"&outfile"';

or

x '"C:\Users\peter\Desktop\vol70_test\Output\tab&sht..xlsx"';

in the end I'd get the error message "ERROR: Physical file does not exist, EXCEL|C:\Users\peter.ahn\Desktop\vol70_test\Output\nsf22323-tab002_test.xlsx\Table
2!R6C2:R102C5."

When I keep the whole string though, it spits out the dataset smoothly to my desired cell ranges.

%let sht=002;

%let outfile=C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx;

OPTIONS NOXWAIT NOXSYNC MISSING=' ';
x '"C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx"';

Any way to work macros into X statements?

[SASKiwi]

Check this link out for the way to run macros: SAS Help Center: DDE Examples

[BigPete]

Just did but I don't see anywhere in there mentioning if there's a way or not to work macros into X statements.

[SASKiwi]

I think you need to run macros in a PUT statement similar to this example:

filename cmds dde 'excel|system';
data _null_;
file cmds;
/* Insert an Excel Macro Sheet */
put '[workbook.insert(3)]';
run;

/* Direct the Output to the Newly created Macro Sheet */
filename xlmacro dde 'excel|macro1!r1c1:r5c1' notab;

data _null_;
file xlmacro;
put '=workbook.name("sheet1","NewSheet")';
put '=halt(true)';
/* Dump the contents of the buffer, allowing us to both write and */
/* execute the macro in the same DATA Step                        */
put '!dde_flush';
file cmds;
/* Run Macro1 */
put '[run("macro1!r1c1")]';
put '[error(false)]';
/* delete the Macro Sheet */
put '[workbook.delete("macro1")]';
run;

The example is about inserting a sheet, but could be modified to run an Excel macro.

[Tom · Accepted solution]

Are you really trying the execute XLSX file as if it was a executable program?  Sounds like kluge.

The error message does not seem to match the X command you showed.  Are you sure the macro variable has the value you think it does? Looks like you might be trying to use the string you need to pass the DDE to tell it where in the sheet to write instead of just the bare filename.  Or perhaps the error was generated by your attempt to use DDE to write to file that was already open (perhaps because you tried to use the X command to open the file).

Your two commands are not generating the same string.  Macro variable references are ignored inside of strings quoted by single quotes on the outside. Use double quote characters instead. If you need to generate the extra quotes as part of the string you are passing to the X command then make sure to double them up.

x """&outfile""";

[BigPete]

Thank you so much, Tom 🙂