topic Re: PROC Report Compute Blocks in ODS and Base Reporting

PROC Report Compute Blocks

deleted_user — Thu, 21 Oct 2010 18:07:10 GMT

Good day SAS programmers,

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
type):

Fund A Fund B Fund C

Sales $15,000 $4,000 $10,000
Service $6,000 $11,000 -$4,000
Revenue $21,000 $15,000 $6,000

Administrative $20,000 $15,000 --
Professional $36,896 $10,000 $5,000
Salaries $56,896.56 $25,000 $5,000

Coffee $17,724 $5,000 $1,000
Supplies -- $5,000 $2,333

Expenses $17,724.42 $10,000 $3,333

RBREAK Summary $95,620.98 $50,000 $14,333

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:

Revenue $21,000
Salaries $56,896.56
RBREAK Summary $35,896.56

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...

Jason Browning
Controller
Lake Superior State University

Re: PROC Report Compute Blocks

Cynthia_sas — Thu, 21 Oct 2010 18:32:38 GMT

Hi:
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??

cynthia

Re: PROC Report Compute Blocks

deleted_user — Thu, 21 Oct 2010 19:05:34 GMT

Hi Cynthia,

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).

PROC FORMAT;
value $acct_type
5 = 'Revenues'
6 = 'Salaries and fringes'
7 = 'Expenses'
8 = 'Transfers'

value $fund_type
'85000U' = 'LSSU Fndn'
'85001U' = 'Unrstrctd Gfts'
'85020U' = 'Cooper Golf Classic'
'85021U' = 'Lake State Golf Classic'
'85030U' = 'Phonathon'
'87001T' = 'Laker Club'
'88303T' = 'Aquatic Lab'
'85514T' = 'SBELS Bldg Cmpgn'
'85516T' = 'ARL Building'
'OTH86' = 'Total Fdn Endwmnts'
'OTHALL' = 'All Other';

RUN;

proc report data=FIN_CALC_NOSIGN nowd;
column ACCT_TYPE FGBOPAL_ACCT_CODE FTVACCT_TITLE FUNDGROUP,SUM_AMOUNT ROW_SUM;
define FUNDGROUP / group ' ' across format=$fund_type. ORDER=internal Style={cellwidth=.52in};
define ACCT_TYPE / group ' ' missing noprint format=$acct_type. ORDER=Internal;
compute before ACCT_TYPE / style=[just=l font=("Times New Roman",9pt,bold)];
text1=(put(ACCT_TYPE,$acct_type.));
line text1 $;
endcomp;
define FGBOPAL_ACCT_CODE / group ' ' format=$5. missing order=internal ;
define FTVACCT_TITLE / group 'Account' ;
define SUM_AMOUNT / ANALYSIS SUM ' ' missing format=comma14.2 ;
define ROW_SUM / 'Total' COMPUTED format=comma14.2;

break after ACCT_TYPE / summarize;
rbreak after / summarize;

COMPUTE ROW_SUM; ROW_SUM = SUM(_C4_,_C5_,_C6_,_C7_,_C8_,_C9_,_C10_,_C11_,_C12_,_C13_,_C14_,_C15_,_C16_); ENDCOMP;

run;

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?

I appreciate any guidance!

Thanks,
Jason

Re: PROC Report Compute Blocks

Cynthia_sas — Thu, 21 Oct 2010 20:28:34 GMT

Hi:
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.

cynthia
[pre]
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;
compute before;
holdF = 111;
holdM = 222;
endcomp;
compute height;
if _break_ = '_RBREAK_' then do;
** change the value on the break line;
** can put more complicated calcs here;
_c3_ = holdF;
_c4_ = holdM;
end;
endcomp;
run;
[/pre]

Re: PROC Report Compute Blocks

deleted_user — Fri, 22 Oct 2010 13:10:03 GMT

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!

Re: PROC Report Compute Blocks

Cynthia_sas — Fri, 22 Oct 2010 13:19:08 GMT

Hi:
You are lucky that someone has already written a similar macro. You will find it in this paper -- http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf starting on page 12/13.

cynthia

Re: PROC Report Compute Blocks

deleted_user — Fri, 22 Oct 2010 14:20:52 GMT

That is the best news all day! Thanks, Cynthia 🙂