BookmarkSubscribeRSS Feed
GeorgeSAS
Lapis Lazuli | Level 10

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Reeza
Super User

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

GeorgeSAS
Lapis Lazuli | Level 10
I can't open the second link.

Thanks.
Reeza
Super User

See the link now. It has full code.

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

 

 

GeorgeSAS
Lapis Lazuli | Level 10

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

 


Thanks a lot!

ballardw
Super User

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.

GeorgeSAS
Lapis Lazuli | Level 10
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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2455 views
  • 0 likes
  • 4 in conversation