topic Re: export with excel function in ODS and Base Reporting

export with excel function

GeorgeSAS — Mon, 17 Oct 2016 15:50:15 GMT

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

Re: export with excel function

RW9 — Mon, 17 Oct 2016 16:47:57 GMT

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;

Re: export with excel function

Reeza — Mon, 17 Oct 2016 19:51:53 GMT

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

Re: export with excel function

GeorgeSAS — Mon, 17 Oct 2016 19:49:28 GMT

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

Thanks a lot!

Re: export with excel function

GeorgeSAS — Mon, 17 Oct 2016 19:50:34 GMT

I can't open the second link.

Thanks.

Re: export with excel function

Reeza — Mon, 17 Oct 2016 19:53:13 GMT

See the link now. It has full code.

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

Re: export with excel function

ballardw — Mon, 17 Oct 2016 22:17:36 GMT

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.

Re: export with excel function

GeorgeSAS — Mon, 31 Oct 2016 15:27:21 GMT

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;