topic Re: proc report partials sums in SAS Programming

proc report partials sums

LukeL — Mon, 27 Jul 2020 15:23:43 GMT

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!

Re: proc report partials sums

PaigeMiller — Mon, 27 Jul 2020 14:44:23 GMT

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.

Re: proc report partials sums

LukeL — Mon, 27 Jul 2020 14:53:31 GMT

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

Re: proc report partials sums

ballardw — Mon, 27 Jul 2020 14:53:24 GMT

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

Re: proc report partials sums

LukeL — Mon, 27 Jul 2020 14:59:26 GMT

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.

Re: proc report partials sums

ballardw — Mon, 27 Jul 2020 15:13:32 GMT

@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;

Re: proc report partials sums

LukeL — Mon, 27 Jul 2020 15:22:42 GMT

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

Re: proc report partials sums

Ksharp — Tue, 28 Jul 2020 12:32:19 GMT

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;