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.
... View more
