DATA Step, Macro, Functions and more

Proc Tabulate

Frequent Contributor
Posts: 140

Proc Tabulate



Can anyone help me by letting me know how I add a "Grand Total" column to my tabulate by adding the totals of each "Class Variable"?


I hope this makes sense




proc tabulate data = output_&ccyymm. missing;

class Pool Pool2 IntRateType Credit_LTV_band Class Arrears_group;

var blended_rate Total_Balance Total_Provision weighted_avg_int_rate weighted_avg_blended_rate term_date promo_end_date accr_int_gross_rate;

table (Pool="Pool")*


(IntRateType="Int Rate Type" all="T")*

(Credit_LTV_band="Credit LTV Band")*

((Total_Balance="Sum of Total Balance"*f=comma19.)

(Total_Provision="Sum of Total Arrears"*f=comma19.)

(accr_int_gross_rate = "Weighted Avg Current Rate"*f=8.2)

(blended_rate="Weighted Avg Future Rate"*f=8.2)

(promo_end_date = "Weighted Average Term Date"*f=ddmmyy10.)

(term_date="Weighted Average Maturity Date"*f=ddmmyy10.)),

(class = "") * (Arrears_group="" all=" Class Total" ) /box="Mortgage Fair Value";




Super User
Posts: 10,538

Re: Proc Tabulate

The  Tabulate keyword ALL will provide summaries for "All" levels of a categorical variable or the entire data set


It isn't clear what you are trying to summarize since you have multiple nesting levels.


Are you looking for (partial)


table (Pool="Pool" All='Pool Summary)*

(Pool2="Pool2" All='Pool 2 summary')*

(IntRateType="Int Rate Type" all="T")*


You may want to show a result (for a subse of the data ) and indicate where you want additional summaries.

Frequent Contributor
Posts: 140

Re: Proc Tabulate


My output currently is (excluding Grand Total)

Adverse     Adverse Total        BTL      BTL Total     Other    Other Total      Grand Total

  4                     4                      3 3             6               1 3             4                      14


The code below gives me the class total broken down by Adverse, BTL, Other


(class = "") * (Arrears_group="" all=" Class Total" )


I need to create the Grand total column that totals - Adverse Total BTL Total Other Total

Super User
Posts: 10,538

Re: Proc Tabulate

are "Adverse Total BTL Total Other Total" Values of the variable Class (bad choice of variable name since many procedures have Class as a statement) ? Are they ALL of the values of Class or only a subset?

If you need a total for ALL of the levels of class then (Class='' All='Class total) should do it.

If you need a summary for a subset you may be able to use a multilabel format but to determine what that would look like then some example data is needed.


Also, do actually intend to SUM date values??

Ask a Question
Discussion stats
  • 3 replies
  • 2 in conversation