Hi,
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")*
(Pool2="Pool2")*
(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";
run;
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.
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
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??
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.