I have what must be a simple formatting issue. I am attempting to
prepare a flash financial statement using SAS from our Oracle
database. I have the data fine, but in PROC REPORT, it adds the
revenues and expenses together - for example (the break is on account
I want to subtract expenses from revenue for the summary line. Is
there an easy way, or a compute block method, to do this? I have
eight columns for different funds, and am generating ODS PDF output.
I can make it work if I make my expenses negative, but I want them to
show as positive on the financial statement.
For example, on the above, I want the following result on the left column of values:
I would like to know if I can accomplish this in PROC REPORT, or if I need to conduct further data pre-processing, etc. (It seems more appropriate for PROC COMPUTAB, but that is not present in our Base 9.3.1 install.) Thanks for your help,
Will try not to bother the forum in the future -- after Googling for two days, I'm out of ideas, which makes me think it's a simple oversight...
Lake Superior State University
Could you make your expense field negative numbers??? That would be simplest -- otherwise, you're right, you'd need to use a COMPUTE block (or 2 or 3) to alter the values at the RBREAK. I'm not at my computer now, but to do this in a COMPUTE block, you'd have to grab and save the FUND totals for expenses at the appropriate break line (save into a temporary variable) and then subtract the saved number from the total at the RBREAK line.
PROC REPORT has an automatic variable called _BREAK_ that you can test in the compute block. There are examples of testing _BREAK_ for things like writing custom break lines ... probably not so many for adjusting the total. When I get back to my computer I will look for an example. How do you determine what Account belongs to what group (Revenue vs Expenses)?? Are there NOPRINT variables that you are using? Can you show your REPORT code??
Unfortunately, I can't display negative numbers on the financial statement (regulatory issue). That's why I'm confused. I've read several proceeding papers on creating ucstom break lines (text, for example), but none that indicate how to adjust the totals in each column.
Here is the code I'm using at present. It's the ACCT_TYPE variable (which is NOPRINT) that lets me determine what is a revenue (5) and what is an expense (others).
5 = 'Revenues'
6 = 'Salaries and fringes'
7 = 'Expenses'
8 = 'Transfers'
If it is possible, I would like to contain it within PROC REPORT. It seems difficult to add a changing number of columns using the object-oriented report writing (which I love otherwise). Maybe stacking tables?
This is the only example that I had on a USB drive -- I'm not at my computer right now. So you can sort of see a number changing for an ACROSS column's value at the break, but it's just an arbirtary change. You can see that at the RBREAK, I am changing the value of HEIGHT to 111 for the Female HEIGHT summary and the value 222 to the Male HEIGHT summary. (I put HEIGHT on the report twice so you could see the originally calculated values versus the changed values.
For what you need to do, you'd have to grab the numbers to subtract (into temporary variables, like I have HOLDF and HOLDM) at the compute after account -- and then at the RBREAK you'd use the "held values" to subtract.
proc report data=sashelp.class nowd;
column age n ('Changed RBREAK Value' sex,height)
('REPORT Calc RBREAK Value' sex,height=htdup);
define age / group;
define sex / across;
define height /sum;
define htdup / sum;
rbreak after / summarize;
holdF = 111;
holdM = 222;
if _break_ = '_RBREAK_' then do;
** change the value on the break line;
** can put more complicated calcs here;
_c3_ = holdF;
_c4_ = holdM;
That looks like what I'm trying to accomplish. However, the number of columns can vary in any given month/quarter, as our funds and activities change (there is a different column for each donor scholarship, for example).
I've been experimenting with trying to incorporate a macro within a compute statement that would count the number of columns (which would be the number of unique FUNDGROUP codes) and then perform a calculation for each. This seems to be more complicated than I am familiar with, however... Any ideas on how we might make it dynamic? If not, I can hard-code.
Thanks so much for pointing me in the right direction!