topic Re: Proc Tabulate in SAS Programming

Proc Tabulate

anonymous_user — Tue, 30 May 2017 15:00:34 GMT

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;

Re: Proc Tabulate

ballardw — Tue, 30 May 2017 15:05:47 GMT

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.

Re: Proc Tabulate

anonymous_user — Tue, 30 May 2017 15:15:14 GMT

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

Re: Proc Tabulate

ballardw — Tue, 30 May 2017 16:10:52 GMT

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??