proc summary by group with total row

[Ronein]

Hello

I have data of loans that customers took.

I am using proc summary to calculate  summary statistics per group (score ).

In the output the summary row appears in first row.

My questions:

How to put the summary total row at the end of the output?

Why the  summary total row get value 0 in score field?

I prefer that the value in score field for total row will be "TOTAL"

 Data Loans;
    format date date9.;
    input CustomerID  date:date9. Sum_Loan  interest score;
cards;
1234 10Jan2019 10 2.1 4
1234 13Jan2019 20 2.2 4
1234 18Feb2019 30 1.9 3
2222 10Jan2019 40 3.5 3
3333 19Jan2019 50 3.7 2
4444 21Feb2019 60 2.9 2
5555 08Jan2019 70 8.0 2
5555 26Feb2019 80 7.0 2
;
run;


proc sort data=Loans;by CustomerID;run;
Data Loans2;
set Loans;
by CustomerID;
Retain Help 0;
if first.CustomerID then Help=Help+1;
Run;


Data Loans3;
Set Loans2;
interest1=interest;
Run;


proc format;
value ffmt
2-3='2--3'
4='4'
5-11='5--11';
run;
options missing = 0;
proc summary data=Loans3  completetypes;
 class score  /preloadfmt;
  format score ffmt.;
    var  sum_loan interest;
    var interest1/weight=sum_loan;
    output out=output_(drop=_type_ _freq_)
     max(Help)=n_customers
     n(Help)=nloans 
     sum(sum_loan)=Total_sum_loans 
     mean( interest1)=wgt_mean_interest
     mean( interest)= mean_interest;
	 format n_customers: comma12.
             nloans:comma12.  
             Total_sum_loans : comma12. 
            wgt_mean_interest :comma12.2
           mean_interest :comma12.2;
run;
proc print data=output_ noobs;run;

[andreas_lds · Accepted solution]

---

@Ronein wrote:

Hello

I have data of loans that customers took.

I am using proc summary to calculate  summary statistics per group (score ).

In the output the summary row appears in first row.

My questions:

How to put the summary total row at the end of the output?

Why the  summary total row get value 0 in score field?

I prefer that the value in score field for total row will be "TOTAL"

---

Proc summary has no option to alter the order of observations, if you wouldn't drop _type_, you could use that to variable in proc sort to get the order you need. And if you don't need the dataset, created by proc summary, for further processing (except for printing), you could switch to proc report.

A minor enhancement to your proc format will show "TOTAL" in the total-row:

proc format;
   value ffmt
      2-3='2--3'
      4='4'
      5-11='5--11'
      . = 'TOTAL'
   ;
run;

[Ronein]

Great!

I applied your solution and it is great!

Data Loans;
    format date date9.;
    input CustomerID  date:date9. Sum_Loan  interest Score;
cards;
1234 10Jan2019 10 2.1 4
1234 13Jan2019 20 2.2 4
1234 18Feb2019 30 1.9 3
2222 10Jan2019 40 3.5 3
3333 19Jan2019 50 3.7 2
4444 21Feb2019 60 2.9 2
5555 08Jan2019 70 8.0 2
5555 26Feb2019 80 7.0 2
;
run;


proc sort data=Loans;by CustomerID;run;
Data Loans2;
set Loans;
by CustomerID;
Retain Help 0;
if first.CustomerID then Help=Help+1;
Run;


Data Loans3;
Set Loans2;
interest1=interest;
Run;


/*Way1*/
proc format;
value ffmt
2,3='2--3'
4='4'
5-11='5--11'
. = 'TOTAL';
run;
options missing = 0;
proc summary data=Loans3  completetypes;
 class Score  /preloadfmt;
  format Score ffmt.;
    var  sum_loan interest;
    var interest1/weight=sum_loan;
    output out=pelet(drop=_type_ _freq_)
     max(Help)=n_customers
     n(Help)=nloans 
     sum(sum_loan)=Total_sum_loans 
     mean( interest1)=wgt_mean_interest
     mean( interest)= mean_interest;
	 format n_customers: comma12.
             nloans:comma12.  
             Total_sum_loans : comma12. 
            wgt_mean_interest :comma12.2
           mean_interest :comma12.2;
run;
proc print data=pelet noobs;run;

/*Way2*/
/*IF we want that total row will be in the end 
  then wouldn't drop _type_ and sort by _type_*/
proc format;
value ffmt
2,3='2--3'
4='4'
5-11='5--11'
. = 'TOTAL';
run;
options missing = 0;
proc summary data=Loans3  completetypes;
 class Score  /preloadfmt;
  format Score ffmt.;
    var  sum_loan interest;
    var interest1/weight=sum_loan;
    output out=pelet(drop= _freq_)
     max(Help)=n_customers
     n(Help)=nloans 
     sum(sum_loan)=Total_sum_loans 
     mean( interest1)=wgt_mean_interest
     mean( interest)= mean_interest;
	 format n_customers: comma12.
             nloans:comma12.  
             Total_sum_loans : comma12. 
            wgt_mean_interest :comma12.2
           mean_interest :comma12.2;
run;
proc sort data=pelet;by descending _type_;Run;
proc print data=pelet noobs;run;