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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.