proc report partials sums

[LukeL]

Hi all,

I have a dataset with some rows made this way:

Group  Class  Subclass  Value
G1     A         A1            1
G1     A         A2            2
G1     A         A3            3
G1     B         B1            4
G1     C         C1            10
G1     C         C2            20
G1     D         D1            20
G1     E         E1            30
G2 ........

What I need to do is a report (proc report?) made this way:

             A      | B  |     C    | D  | E  |
Group   A1 A2 A3 AT | B1 | C1 C2 CT | D1 | E1 | T
G1      1  2  3  6  | 4  | 10 20 30 | 20 | 30 | 90
G2 ....

where AT/CT are the totals for the classes A and C, while T would be the grandtotal for the entire group.

I'm trying using the proc report, but not able to obtain the subtotals on rows for only gruops with more than one value... Any idea? Currently proc report is something like

proc report data=mydata missing;
    columns GROUP (CLASS,(SUBCLASS,VALUE) ('Subtotal' VALUE))  ('Total' VALUE=TOT);
    define group / group;
    define class / across '';
    define subclass / across '';
    define VALUE/ analysis '';
    define TOT/ analysis '';
run;

but in this ways all values in cols B* D* E* are duplicated...

Thanks!

[PaigeMiller]

I would compute the cumulative sums in a data step, and then use PROC REPORT for the final report. That seems much easier to me.

[LukeL]

Yes, that would be my way, but that way I couldn't use across anymore, having to compute all the columns and list them on the report.... So was wondering if there was a more straightful way to le a proc (repoort/tabulate) do all he stuff... But probably not... 🙂

[ballardw]

First start with representative data. Your desired output implies either a variable or something with the value G1 in the variable Group but you do not show it anywhere in your data.

If AT is supposed to be the totals of the A values, say so, same with the C

Probably any approach that works strictly within proc report will have a "total" for B,D and E as well. The proc does not have a lot of "condtionally create column" features

[LukeL]

implies either a variable or something with the value G1 in the variable Group but you do not show it anywhere in your data.

If AT is supposed to be the totals of the A values, say so, same with the C

Sorry, my mistake, forgot to put the gorup in the data, corrected the first post, and added the clarification for the partials AT and CT.

[ballardw]

---

@LukeL wrote:

implies either a variable or something with the value G1 in the variable Group but you do not show it anywhere in your data.

If AT is supposed to be the totals of the A values, say so, same with the C

Sorry, my mistake, forgot to put the gorup in the data, corrected the first post, and added the clarification for the partials AT and CT.

---

Now get your CLASS and SUBCLASS values to match your output. You have all of the example data with Class=A but show B1 as subordinate to a Class=B.

Assuming the data actually looks more like this, then the result from Proc Tabulate is the closest I can get.

data have;
 input Group $  Class $  Subclass $  Value;
datalines;
G1     A         A1            1
G1     A         A2            2
G1     A         A3            3
G1     B         B1            4
G1     C         C1            10
G1     C         C2            20
G1     D         D1            20
G1     E         E1            30
;

proc tabulate data=have;
   class group class subclass  ;
   var value;
   table group,
         (class*(subclass All='Group total') All='Total')
             *value=''*sum=''
         /
   ;
run;

[LukeL]

Ok, the result is the same I've would have with the use of the proc report... Sorry for the wrong data, I really need some holidays I suppose... 😓

[Ksharp]

ballardw  gave you right code. If you want PROC REPORT.

data have;
 input Group $  Class $  Subclass $  Value;
datalines;
G1     A         A1            1
G1     A         A2            2
G1     A         A3            3
G1     B         B1            4
G1     C         C1            10
G1     C         C2            20
G1     D         D1            20
G1     E         E1            30
;
proc report data=have nowd;
columns Group   Class,(Subclass,Value  Value=v) Value=total;
define group/group;
define class/across ' ' nozero;
define Subclass/across ' ' nozero;
define value/analysis sum ' ' ;
define v/analysis sum 'total';
define total/analysis sum 'grand total';
run;