From DDE to ODS Excel - How to position 2 tables at different location on the same spreadsheet

[AudeP]

Dears,

I've been asked to find a solution in order to prevent any further use of DDE in my company. 

I've done a lot of test using ods excel options (sheet_name, sheet_interval, start_at) and PROC REPORT (for the noheader option) to reproduce what was done with DDE. However i 'm stuck because DDE allows to choose multiples position when writing SAS tables to Excel. On the other side, with ODS Excel, once "start_at" option is set, it can't be changed later. Is there some another way to allow writing two different tables on the same spreadsheet at chosen position? As an example, i should be able to write the first table(10 rows) at column 3, row 7 (ok no prob), then the second one at column 3, row 30...

Here is an subset of "DDE" program i should convert to ODS Excel: 

filename cell1 dde "Excel|[draft.xlsm]dde DM P!R7C3:R7C4" notab;

data _null_;
        file cell1;
        set dset1(where=(type='PARTICUL'));
        retain ht '09'x;
        put nbre_oth ht nbre_oth2   ;
run;

......

filename cell1 dde "Excel|[draft.xlsm]dde DM P!R30C3:R34C4" notab;

data _null_;
        file cell1;
        set dset1(where=(type='PARTICUL'));
        retain ht '09'x;
        put nbre_smth  ht nbre_smth1   ;
run;

So, indeed i can set the first table at the same kind of position,but not the second one:

ods listing close;
ods excel file="someDummyPath\Test_ODS.xlsx" options(sheet_name="dde DM P" sheet_interval="none" start_at="3,7");
proc report data=dset1(where=(type='PARTICUL')) noheader nowd;
column nbre_oth nbre_oth2 ;
define nbre_oth/DISPLAY;
define nbre_oth2/DISPLAY;
run;
ods excel options(start_at="3,30"); /*not working as indicated in documentation*/
proc report data=dset1(where=(type='PARTICUL')) noheader nowd;
column nbre_smth nbre_smth1;
define nbre_smth/DISPLAY;
define nbre_smth1/DISPLAY;
run;
ods excel close;

To this point, i searched the web but was not able to find a workaround. And I don't know if there are other methods allowing to reproduce what my colleagues currently produce using DDE (multiple tables written at different position, on same and different spreadsheets + multiple update of the same excel file). To give you some more context, they use a excel template file containing lots of formula and all they need is to fill specific cells each month with SAS data they have computed. This is why position is so important here. 

Any advice on this issue is more than welcome. Thank you very much. 

[Tom]

Why not just modify the template so that each target location for writing the data is a separate sheet?

[AudeP]

Thank you for your answer Tom. Indeed, if I could have modified the template, i would have worked differently. But maybe you guessed it, this request comes from a "business" section of my company. Moreover, we are talking about a template built years ago, used but a lot of different people, and having lots of inextricably linked spreadsheets. 

[DWilson]

I don't know if this is possible with SAS without DDE. The solution my company came up with to replace DDE is to use R with SAS.

Use SAS/IML to push data to be written to excel to R

Use SAS/IML to submit R code to R that actually writes the data to an excel file

We use either the XLConnect R package or the Openxlsx R package to support writing to excel files.

See this paper for example code to get you started:
https://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-109_Final_PDF.pdf