export with excel function

[GeorgeSAS]

Hello,

I want to export and add a function at cell E21: "=SUM(E2:E20)"  to calculate total weight of all people.how to do it in SAS??

I don't want the result from SAS sum function or proc means from SAS, I only want excel output with excel function in that cell.

SAS code:

proc export

data=sashelp.class

dbms=xlsx

outfile="c:\temp\class1017.XLSx"

replace;

run;

Thanks

[RW9]

Use ods tagesets.excelxp (or the other one), and set the attribute of the tag i.e:

compute result;

  if category="total" then call define (_COL_,'style', 'style={tagattr="formula:SUM(E2:E20)"}');

endcomp;

[Reeza]

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

Make sure to set the Formulas=Yes option to within tagsets as well.

See the demo for an example with formulas

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

[GeorgeSAS]

I can't open the second link.

Thanks.

[Reeza]

See the link now. It has full code.

Note that formulas need to use RowColumn referencing, A### referencing typically seen in Excel.

[GeorgeSAS]

Would you please give me the full program SAS code? I can't figured it out.

Thanks a lot!

[ballardw]

Another option, assuming you are summing the values of a column could be to use proc print with a SUM statement.Then the value is calculated by SAS before export and no issues with the Excel formula at all.

[GeorgeSAS]

ods listing close;
ods tagsets.excelxp file="test.xls" style=statistical

options(auto_subtotals='yes' ABSOLUTE_COLUMN_WIDTH='8,8,7,9,9,8,12,6,15,15,9'
frozen_headers='yes' sheet_name='CHECK'
autofilter='all' );

proc print data=p1 noobs label split='*';
*id a b c;
var Mai class MON REV PI a b c
;
var diff/style={tagattr='formula:RC[-6]/RC[-5]'};
sum rev pi wei;
label

CLASS='_CLASS * * '


diff='Rev*Per*Pi * * '
;
run;quit;
ods tagsets.excelxp close;
ods listing;