topic Re: send excel subtotal function to excel in ODS and Base Reporting

send excel subtotal function to excel

deleted_user — Thu, 08 Nov 2007 21:46:55 GMT

Hello,
Is there a way to create an excel function in SAS and then send it to Excel via ODS?
Specifically, I'd like to subtotal my data and have this show up in Excel as a subtotal - not a value.
Right now, I am stuck between either having to do create subtotals in proc report and send them over to Excel as values, or just creating a list and having to manually subtotal it in Excel afterwards.
If there was a tagset option to do an XML subtotal that Excel recognizes, that would be great. If I can do a style over-ride to forcefeed it into Excel, that is fine too, if someone can point me in the right direction.
Thanks,
Joe

Re: send excel subtotal function to excel

Cynthia_sas — Thu, 08 Nov 2007 23:39:53 GMT

Hi:
Look at the example of using the TAGATTR attribute in a STYLE override with TAGSETS.EXCELXP here:
http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#formats

Notice that the formula is in Row/Column notation and not A1..A14 notation.

I believe you will also need to download an updated copy of the ExcelXP tagset. I don't believe that formulas worked in the original tagset that was shipped with SAS 9.1.3 -- but Tech Support could help you with that.

The issue with the example is that they are using proc print to touch every row with a formula and you want to change the subtotal line. So it's good that you're using PROC REPORT. You can send a formula along with the value -- so that if somebody changes a number, the subtotal automatically changes -- using the same TAGATTR syntax as shown in the Tech Support note.

I'm not a real whiz with Excel, so I don't know how you get around needing to know how many rows to include in the formula, but this example worked for me (assuming only 2 report rows and 1 header and 2 subtotal/total line. I always use =SUM in Excel, so I'm not sure what you mean by the SUBTOTAL function.

Perhaps Tech Support can provide more help.

cynthia

[pre]

data prdsale;
set sashelp.prdsale;
where country = 'GERMANY';
run;

ods tagsets.excelxp file='formula2.xls' style=statistical;

proc report data=prdsale nowd split='*'
style(header)={font_size=10pt font_weight=bold};
column country region predict actual;
where country = 'GERMANY';
define country / group;
define region / group;
define predict /'Predicted*Sales' style={tagattr='format:Currency'};
define actual / 'Actual*Sales' style={tagattr='format:Currency'};

compute predict;
** note for this formula, you have to know how report rows there are;
** and how many header rows there are in the final worksheet;
if _break_ = '_RBREAK_' then do;
Country = 'Total';
call define (_col_,'STYLE',
'style={tagattr="format:Currency formula:=SUM(R[-2]C:R[-1]C) "');
end;
endcomp;

compute actual;
if _break_ = '_RBREAK_' then do;
call define (_col_,'STYLE',
'style={tagattr="format:Currency formula:=SUM(R[-2]C:R[-1]C) "');
end;
endcomp;

rbreak after /summarize;

run;

ods tagsets.excelxp close;

[/pre]

Re: send excel subtotal function to excel

JackHamilton — Mon, 12 Nov 2007 19:36:01 GMT

As Cynthia said, it's good that you're using PROC REPORT, because you can feed the SUBTOTAL function (or the SUM function) to Excel.

I create worksheets with filters, and use this code to put a filtered subtotal at the top of the page:

compute before _page_;
line '="Membership Count after Filtering: '
'"&TEXT(SUBTOTAL(9,r5c1:r'
"%sysevalf(&dhmo_filtered_SUMMARY_ROWS.+4, integer)"
'c1),"#,##0")';
endcomp;

where an external process has figured out the number of rows. I suppose it would be easier to just hardcode something like

=subtotal(9, r5c1:r65536c1)

but there might be efficiency considerations.

Re: send excel subtotal function to excel

tbatliner — Wed, 13 Feb 2008 12:04:57 GMT

Hi,

I'm trying to create an xls-File with a Formula. But when I run the program EGuide hangs...

Code:
proc report data=READY4EXCEL headskip missing nowd;
column ('Material Properties' PG IG matnum matdesc laundat globretdat pp rrp mape ) ('Order Quantity' timeid,(stock sonst)) ('Billed Qty' sales),timeid ('FC Qty' predict),timeid revFC deviation comment;
define pg / group 'PG' width=2;
define ig / group 'IG' width=2;
define matnum / group 'Material' width=8;
define matdesc / group 'Description' width=20;
define laundat / group 'Laun Date' width=8;
define globretdat / group 'Ret Date' width=8;
define pp / group 'PP' width=3;
define rrp / group 'RRP' width=3;
define mape / group 'mape' width=5;
define revFC / computed 'Revised FC' width=4;
define deviation / computed 'Dev in %';
define comment / computed 'Comment' width=10;

define timeid / across '' format=monyy5. order=data;
*define oi / analysis sum '';
define stock / analysis sum 'Stock';
define sonst / analysis sum 'DD';
define sales / analysis sum '';
define predict / analysis sum '';

compute revFC / length=8;
revFC =;
endcomp;

compute deviation;
call define(_col_,'style',"style={tagattr='Formula:=IF(RC[-2]>0,RC[-1]/RC[-2]-1,1)'}");
endcomp;

compute comment / char;
comment="";
endcomp;

where pg = "11" and timeid ne .;
run;

Any idea would help,
thanks and best regards,
Thomas