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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.