SAS to EXCEL adding formulas

[SanjayM]

Hello,

I wish to output excel file having formulas.
Simple formulas like RC[-1] - RC[-2] works as they have to reference the same row and does not come across any text values.

But if I want to add a formula that references the previous row of the same cell then it doesn't work as the first row is the label and the computation fails to zero value. Can 'IF' conditions be added as formulas. Example: I want to add logic that If the previous row has text / label value then set it to zero. i.e
tagattr='format: formula:IF(R[-1]="running_sales",0,R[-1])C+RC[-1]'
But this dosen't work.

How can I make it to work, is there any better way to do it.

Please see the below code that fails.


data prdsale;
set sashelp.prdsale;
running_sales = 0;
run;

proc sort data=prdsale; by country region division year; run; quit;

title; footnote;

ods listing close;

ods tagsets.excelxp file='c:\formulas.xls' style=statistical;


proc print data=prdsale noobs label split='*';
where country eq 'CANADA' and year eq 1993;
id country region division;
var prodtype product quarter month year;
sum predict ;
sum actual ;
/*Calculate the runnings sales by adding current
sales with previous rows sales value*/
sum running_sales / style={tagattr='format: formula:R[-1]C+RC[-1]'};
label prodtype = 'Product*Type'
predict = 'Predicted*Sales'
actual = 'Actual*Sales';
run;

ods tagsets.excelxp close;

ods listing;

[Cynthia_sas]

Hi:
Not sure about the conditional logic in the formula, but you may not need a formula. If all you want to do is calculate a running total, and you are trying the formula approach because you can't get a running total with PROC PRINT -- then you should know that you CAN calculate a running total in a report column using PROC REPORT - - specifically with a COMPUTE block. So my question is -- if a SAS procedure would create a column with the running total value, would you still use TAGATTR???

Also, a second question...it makes sense to me to add up or summarize PREDICT and ACTUAL -- but if RUNNING_SALES is a cumulative number already, it doesn't make sense to summarize it at the end of the report. Do you really want a summary of the cumulative column??? Or do you just want the summary line to show the same number as the last report row??? Or, do you want just a blank on the summary row???

And, a comment -- it doesn't make sense to me to put the TAGATTR override on the SUM statement --conceptually, the SUM statement only has an impact on the last row of the report (or the last row of a BY group) -- but, it seems to me that you need for the formula to be attached to every row except the first non-header row on the report. Again, you could implement this type of logic in a COMPUTE block with PROC REPORT. In pseudo-code syntax, what you would have inside the COMPUTE block would look something like this:
[pre]
COMPUTE running_sales;
...more code....
if <on the first report row> then running_sales=<first value for ACTUAL> ;
else if <not on the first report row> then do;
running_sales = 0;
call define(_col_,'style','style={tagattr="formula:R[-1]C+RC[-1]"}');
end;
endcomp;
[/pre]

Anyway, you can do the formula if you want to, but you might not need to if you switched to PROC REPORT.

cynthia

[SanjayM]

Hi Cynthia,

Sorry to confuse you. The example posted here is just a sample of what I intend to use. It definately does not make sense to do running total of the sales but actual in my report I want to calculate running balance.

I want to output the excel report with formulas in it because the user needs the ability to manually alter the balance and it should affect the balance of the following rows. i.e. adjusting the balance at a point to affect the balance in the remainder of the rows down. That's why I want to use TAGATTR.

I can't get this working. If you could throw some more syntax light please it will be greatly appreciated.

For ease of understanding I would simplify the example by just using a amount column and then calculate the balance which would be Previous Amount + Amount.

Regards
San

[Cynthia_sas]

Hi:
Explaining more about your real intentions and data would have been useful from the beginning. However, it's water under the bridge now.

Rather than retooling an example at this point in time, I have some code that calculates cumulative sales using TAGATTR and you can alter the formula, as needed. The example uses a subset of SASHELP.PRDSALE, so it's not that different from what you posted. The key is that PROC REPORT is used instead of PROC PRINT.

With PROC REPORT, I can use a COMPUTE block to test which report row (based on the CNTROWS item) is currently being handled by PROC REPORT if it is the first report row, then the value is set to the same as ACTUAL.SUM -- you will probably have a starting balance or similar value to put here instead. Then, if CNTROWS is GT 1, the formula is attached to the cell in the CALL DEFINE statement for the second COMPUTED item (SHOW_CUM_SALES).

For my report, the value at the summary line is changed to missing (.) and then the SAS options turn missing to ' ' (instead of showing the bogus summary). You will probably put a different formula here in your TAGATTR. The test for '_RBREAK_' is what allowed me to reset the value of my cumulative column on the final summary line.

PROC REPORT is more flexible than PROC PRINT for allowing you to "hide" report items using NOPRINT -- which is how CNTROWS is treated. PROC REPORT also allows the use of "temporary" variables (whose values are automatically retained) to help you create other report items in COMPUTE block. (This is what HOLD and CUMSALES are.) _BREAK_ is an automatic variable that PROC REPORT uses to identify report rows that come from break processing, as opposed to report rows that come from the data.

The compound name, ACTUAL.SUM, is the referencing syntax that PROC REPORT requires inside a COMPUTE block, and ONLY inside a COMPUTE block. Note that in the COLUMN statement and in the DEFINE statement, the variable name of ACTUAL is used.

If you are new to PROC REPORT, you'll have to spend some time figuring out how PROC REPORT works and how it differs from PROC PRINT. The REPORT documentation and user group papers mentioned in previous postings will be very useful to you. For example, some paper links were posted here:
http://support.sas.com/forums/thread.jspa?messageID=17771䕫
http://support.sas.com/forums/thread.jspa?messageID=49404샼
cynthia
[pre]
** make some data similar to example data;
proc sort data=sashelp.prdsale out=prdsale;
by division prodtype product;
where country eq 'CANADA' and year eq 1993;
run;

title; footnote;

ods listing close;
options missing=' ';

ods tagsets.excelxp file='c:\temp\formula_cum_sales.xls' style=statistical
options(absolute_column_width='10');

proc report data=prdsale nowd split='*';
title 'Create SHOW_CUM_SALES report item entirely in PROC REPORT';
column cntrows division prodtype product actual show_cum_sales;
define cntrows / computed noprint;
define region / display;
define prodtype /display 'Product*Type';
define product / display;
define actual / sum 'Actual*Sales';
define show_cum_sales / computed f=dollar14.0 'Cum Sales';
compute cntrows;
hold + 1;
cntrows = hold;
endcomp;
compute show_cum_sales;
if _break_ ne '_RBREAK_' then do;
cumsales + actual.sum;
if cntrows = 1 then show_cum_sales=cumsales;
else if cntrows gt 1 then do;
show_cum_sales = 0;
call define(_col_,'style','style={tagattr="formula:R[-1]C+RC[-1]"}');
end;
end;
else if _break_ eq '_RBREAK_' then do;
show_cum_sales = .;
** put your formula in a call define here for the sum of the running balance;
** call define(_col_,'style','style={tagattr="formula:????"}');
end;
endcomp;
rbreak after / summarize style={font_weight=bold};
run;

ods tagsets.excelxp close;
[/pre] corrected typo

Message was edited by: Cynthia@sas

[SanjayM]

Hi Cynthia.
Million Thanks.
You are just great.