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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: