how to use excel formulas in dde

[Karthik95]

options noxwait noxsync ;
X '"C:\Program Files (x86)\Microsoft Office\root\Office16\excel.EXE"';
data _null_;
X=SLEEP(2);
run;

filename stansys dde 'excel|system';
data _null_;
file stansys;
put '[open("C:\Users\karthik\Documents\stansys1.xlsx")]';
run;

filename stansys1 dde 'excel|sheet2!r9c6:r16c12' notab;
data _null_;
set work.ba;
file stansys1;
put noncoveramt '09'x // billedamt '09'x ///copayamout '09'x //finamount '09'x ;
run;

filename stansys1 dde 'excel|sheet2!r9c7:r16c12' notab;
data _null_;
set work.ba;
file stansys1;
where busdt='01feb2019';
put noncoveramt '09'x // billedamt '09'x ///copayamout '09'x //finamount '09'x ;
run;

filename stansys1 dde 'excel|sheet2!r9c8:r16c12' notab;
data _null_;
set work.ba;
file stansys1;
where busdt='01mar2019';
put noncoveramt '09'x // billedamt '09'x ///copayamout '09'x //finamount '09'x ;
run;
filename stansys1 dde 'excel|sheet2!r9c9:r16c12' notab;
data _null_;
set work.ba;
file stansys1;
where busdt='01apr2019';
put noncoveramt '09'x // billedamt '09'x ///copayamout '09'x //finamount '09'x ;
run;
filename stansys1 dde 'excel|sheet2!r9c10:r16c12' notab;
data _null_;
set work.ba;
file stansys1;
where busdt='01may2019';
put noncoveramt '09'x // billedamt '09'x ///copayamout '09'x //finamount '09'x ;
run;
filename stansys1 dde 'excel|sheet2!r9c11:r16c12' notab;
data _null_;
set work.ba;
file stansys1;
where busdt='01jun2019';
put noncoveramt '09'x // billedamt '09'x ///copayamout '09'x //finamount '09'x ;
run;
filename stansys1 dde 'excel|sheet2!r9c12:r16c12' notab;
data _null_;
set work.ba;
file stansys1;
where busdt='01jul2019';
put noncoveramt '09'x // billedamt '09'x ///copayamout '09'x //finamount '09'x ;
run;

filename stansys dde 'excel|system';
data _null_;
file stansys;
put '[save.as("C:\Users\karthik\Documents\stansys1.xlsx")]';
put '[close]';
put '[quit]';
run;

data ba;
input noncoveramt billedamt copayamout finamount busdt$9.;
cards;
12.8 13.9 67.89 56.9 01jan2019
12.8 13.9 67.89 56.9 01feb2019
12.8 13.9 67.89 56.9 01mar2019
12.8 13.9 67.89 56.9 01apr2019
12.8 13.9 67.89 56.9 01may2019
12.8 13.9 67.89 56.9 01jun2019
12.8 13.9 67.89 56.9 01jul2019
run;

[Cynthia_sas]

Hi:
This really isn't an ODS or reporting question. When you use DDE, you send DDE commands from SAS to Excel using a DATA step program.

How you send formulas will be specific to how you specify a formula in a DDE command. You might want to look for papers or documentation about using DDE or open a Track with Tech Support.

Cynthia

[Karthik95]

yes

[DWilson]

https://www.mwsug.org/proceedings/2010/excel_db/MWSUG-2010-166.pdf