DATA Step, Macro, Functions and more

Proc Tabulate

Reply
anonymous_user
Posts: 0

Proc Tabulate

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;

 

 

Super User
Posts: 11,343

Re: Proc Tabulate

Posted in reply to anonymous_user

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.

anonymous_user
Posts: 0

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: 11,343

Re: Proc Tabulate

Posted in reply to anonymous_user

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
  • 98 views
  • 0 likes
  • 2 in conversation