The SAS Output Delivery System and reporting techniques

export with excel function

Reply
Regular Contributor
Posts: 207

export with excel function

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

Super User
Super User
Posts: 7,403

Re: export with excel function

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="formulaSmiley FrustratedUM(E2:E20)"}');

endcomp;

Super User
Posts: 17,840

Re: export with excel function

[ Edited ]

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

Regular Contributor
Posts: 207

Re: export with excel function

I can't open the second link.

Thanks.
Super User
Posts: 17,840

Re: export with excel function

See the link now. It has full code.

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

 

 

Regular Contributor
Posts: 207

Re: export with excel function

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

 


Thanks a lot!

Super User
Posts: 10,500

Re: export with excel function

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.

Regular Contributor
Posts: 207

Re: export with excel function

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;
Ask a Question
Discussion stats
  • 7 replies
  • 429 views
  • 0 likes
  • 4 in conversation