How to put totals before details in a report

[deleted_user]

I have a report listing information about adjunct faculty, that I want to be able to have totals show before and after details, as follows:

Position Name Account
====== ==== ======
............................................... Salaries Liquidated Difference (yes, they want these lines like this)
............................................... ====== ======= =======
............................................... 12,000 1,500 10,500

WorkerB.....Tom............1234
Loafer.........Mary...........1234
Boss..........Ralph.......... 0001

Total for WorkerB: 1
Total for Loafer....: 1
Total for Boss.....: 1

Total for Account 1234 : 2
Total for Account 0001 : 1

I've tried a few things, but nothing seems to work.... I tried compute before, sum, compute after, and unfortunately think I've coded myself into confusion. By the way, I am not using macros to try this. If anyone has an idea (or two or three), would you mind sharing them with me?

Thank you so much!

[Cynthia_sas]

You really CAN do this kind of report with either PROC REPORT or with a DATA step program. Interestingly enough, a similar question was recently posted to the EG forum and this posting might prove useful to you:
http://support.sas.com/forums/thread.jspa?threadID=1714&tstart=0
to help explain the SAS report procedures and how you can get a detail report and/or a summary report and/or a detail report with summary data.

For this posting, I thought it would be easier for this person to get the kind of fine control they wanted by having a DATA step program to produce output. So if you look at the example program, you would use similar logic in a DATA step program to get both "before" and "after" information around the detail rows.

However, you might not get EXACTLY the kind of indentation you want to see for those "summary" lines. That's because the kind of output that is written by default is tabular in nature -- that means the same number of columns for EVERY row on the page or a LINE statement (REPORT) whose output spans ALL the columns on the page. (Also, the row of equal signs under the headings -- probably not going to get that as stored process output -- using ODS).

Looking at your report example, I see a few things to point out. It looks to me like you might have Salaries for every worker, but do you actually have a Liquidated column for every worker. In other words -- is the 'Salaries' number the sum of each person's individual salary? What about Liquidated, what does this come from?

Generally, if you were trying to do this with PROC REPORT, it would involve a lot of compute block processing to hold values. AND, if the values, such as Liquidated and Difference are coming from a different file or are calculated from a different dataset, then to do the report with PROC REPORT might involve massaging the DATA in a DATA step program, anyway.

So, if you were going to be inside a DATA step, you may as well write out the report with one pass through the data, if possible. This kind of making counter variables to hold totals for before/after the detail might prove easier to do in DATA step where you can test for FIRST.byvar and LAST.byvar -- and, depending on how the top line of information is coming from -- you can write that out for every group or high-level BYVAR. (By FIRST.byvar, I mean that if you have BY DEPARTMENT in the data step program, you can test for the value of FIRST.DEPARTMENT and LAST.DEPARTMENT.)

You might consider contacting Tech Support for help with this one. Especially because working with PROC REPORT LINE statements in stored processes might prove problematic in Web Report Studio.

cynthia

[deleted_user]

Hi, Cynthia,

Thanks for your help! Yes, we actually do have columns for every person related to liquidated data. Liquidated is used to account for monies expended for unforeseen / incidental expenses and as a rule, might be taken from encumbered monies. Also, yes the 'Salaries' number is the sum of each person's individual salary, by college by department by account. I'll take a look at the link you pointed me to and see what I can fathom from it. I think I'm close, but.......

Anyway, thanks once more!!