Proc Report - Subtotal Columns

[cbal324]

Hi all - I have a specific report format that I am trying to achieve using PROC REPORT, and I'm getting pretty close, but there's a column subtotal element that I cannot figure out.

I have sales data, summarized by Client, Quarter, and Week.  Each quarter contains an arbitrary number of weeks, up to around 13.

I would like to list Client information down the side, with:

• Quarters listed across the top,
• each Quarter's corresponding Weeks nested underneath,
• the table populated with Sales for each Client-Week,
• and Grand Totals on the far right and bottom.  I've gotten this far on my own just fine.

Where I'm getting stuck is that I'd also like to show Quarterly subtotal columns, at the end of each Quarter.  I have tried different variations of "break after" and the like, but I'm just not getting there.

Does anyone in the community have any suggestions?  Below is some sample code that represents the progress I've made so far.  I've also attached an image showing what I currently "have" versus what I "want."  I greatly appreciate any and all help - cheers!

data one;
input client_rank client $9. client_id quarter $ weekending sales;
datalines;
1 Apple 12345 Q1 20240106 1000
1 Apple 12345 Q1 20240113 2000
1 Apple 12345 Q1 20240127 5000
1 Apple 12345 Q2 20240413 3000
1 Apple 12345 Q2 20240420 4000
1 Apple 12345 Q2 20240427 2000
2 Microsoft 67890 Q1 20240106 3000
2 Microsoft 67890 Q1 20240113 1000
2 Microsoft 67890 Q1 20240127 2500
2 Microsoft 67890 Q2 20240413 4000
2 Microsoft 67890 Q2 20240420 500
2 Microsoft 67890 Q2 20240427 1500
;
run;

proc report data = one nowd;
column client_rank client client_id quarter, weekending, sales ("Total" sales=tot);
define client_rank / noprint group;
define client / "Client" group;
define client_id / "Client ID" group;
define quarter / " " across;
define weekending / " " across nozero;
define sales / "Sales" analysis sum format=comma12.0;
define tot / "Sales" analysis sum format=comma12.0;

rbreak after / summarize;
compute after;
client = "Total";
endcomp;
define client_rank / order group;
run;

[ballardw]

Before going on to some possible solutions there are some questions that need answers.

First, I see Quarter and Weekending but with only 3 weekending values in a quarter. Will you have more weeks in your actual data? Will perhaps the 'last' quarter have a different number of weeks than other quarters? (This answer goes to one of the ways that Proc Report sums columns)

Is there a particular reason that Weekending is just a number and not set up as actual SAS date values?

One approach, that simplifies the "column total" is custom multilabel format:

proc format library=work;
value weekendingtr (multilabel)
20240101-20240330 = 'Subtotal'
20240101-20240330 = [f8.]
20240401-20240630 = 'Subtotal'
20240401-20240630 = [f8.]
;
run;

proc report data = one nowd;
column client_rank client client_id quarter, weekending, sales ("Total" sales=tot);
define client_rank / noprint group;
define client / "Client" group;
define client_id / "Client ID" group;
define quarter / " " across;
define weekending / " " across nozero format=weekendingtr. mlf;
define sales / "Sales" analysis sum format=comma12.0;
define tot / "Sales" analysis sum format=comma12.0;

rbreak after / summarize;
compute after;
client = "Total";
endcomp;
define client_rank / order group;
run;

Such a format needs a little bit of explaining as most procedures will not use the multiple label approach. You define range of values that provide different display values. In this case the "quarter" as a whole gets "Subtotal" and the individual Weekending values get something that displays the individual value.

Note that the way I have done this provides for additional Weekending values in the quarters and will adjust if there are more "weekending" values for some quarter than for others.

If this were my project I would make the Weekendingqtr format include a longer range of values so I don't forget to change it when the Qtr3 data shows up.

Only Procs Report, Tabulate, Means and Summary

The real change I would make would be to have the Weekending values actual SAS date values because it would be possible to get a variety of different reports just by changing the format a bit easier as SAS has formats to do year, quarter, month, week (with some caveats).

[Ksharp]

I think to render this kind of pivot table ,the bese choice is using PROC TABULATE.

 

data one;
input client_rank client $9. client_id quarter $ weekending sales;
datalines;
1 Apple 12345 Q1 20240106 1000
1 Apple 12345 Q1 20240113 2000
1 Apple 12345 Q1 20240127 5000
1 Apple 12345 Q2 20240413 3000
1 Apple 12345 Q2 20240420 4000
1 Apple 12345 Q2 20240427 2000
2 Microsoft 67890 Q1 20240106 3000
2 Microsoft 67890 Q1 20240113 1000
2 Microsoft 67890 Q1 20240127 2500
2 Microsoft 67890 Q2 20240413 4000
2 Microsoft 67890 Q2 20240420 500
2 Microsoft 67890 Q2 20240427 1500
;
run;
proc tabulate data=one format=comma32.;
class client client_id quarter weekending;
var sales;
table client*client_id all, quarter=''*(weekending='' all='SubTotal')*sales*sum='' all*sales*sum='';
run;

[Cynthia_sas · Accepted solution]

Hi:

  You can also do a Subtotal column with PROC REPORT very similar to how you generated the last column for Total. You just needed to get the nesting correct (which is why I color coded the parentheses.

  (I deleted your extra define statement for client_rank because it was not necessary.)

Cynthia

[cbal324]

Greatly appreciate everyone's thoughtful response!  Cynthia's response was most precisely what I was going for (I need to brush up on my nesting), but I really like the additional thoughts on using Proc Format to show the date range in my subtotals, as well as considering Proc Tabulate next time for nested totaling/subtotaling.  Depending on client requirements, I may circle back to some of those ideas.  Thanks everyone!