topic Re: proc summary with user defined format for loans data in SAS Programming

proc summary by group with total row

Ronein — Tue, 11 Jun 2019 06:01:41 GMT

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;

Re: proc summary with user defined format for loans data

andreas_lds — Tue, 11 Jun 2019 06:11:28 GMT

@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;

Re: proc summary with user defined format for loans data

Ronein — Tue, 11 Jun 2019 06:27:39 GMT

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;