BookmarkSubscribeRSS Feed

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;

 

 

3 REPLIES 3
ballardw
Super 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.

 

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 654 views
  • 0 likes
  • 2 in conversation