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
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;
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
See the link now. It has full code.
Note that formulas need to use RowColumn referencing, A### referencing typically seen in Excel.
Would you please give me the full program SAS code? I can't figured it out.
Thanks a lot!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.