topic Re: two dimensional report with 2 varaibles in y and one in X in SAS Programming

two dimensional report with 2 varaibles in y and one in X

Ronein — Mon, 13 Jan 2020 07:29:23 GMT

Hello

I need to create a summary report that will look like that:

The raw data table contains following fields:

Customer ID, Model,Team,SUMLOAN ,interest, LenghtLoan

I want to create a summary report that will look like that:

In x dimension will have categories of field TEAM (There are 3 categories: X/Y/W).

In Y dimension will have categories of Model field (2 possible categories : A/B) and under it categories of field LenghtLoan

(2 possible categories :above 365 / below 365).

Need to calculated 3 fields:

N_records

SUM OF SUMLOAN

weighted average of interest

I want also to include TOTAL Row in the report

Data  rawtbl;
INFILE DATALINES DLM=',';
input ID MODEL $ Team $	SUMLOAN	interest LenghtLoan;
cards;
1,A,W,10,2.4,180
2,A,Y,20,2.3,180
4,A,X,40,7.8,720
9,A,X,90,4.1,3650
10,A,Y,100,5.2,180
11,A,X,110,4.8,180
12,A,W,120,2.7,720
18,A,X,180,2.9,3650
19,A,Y,190,4.1,3650
20,A,W,200,3.9,3650
3,B,X,30,3.4,180
5,B,Y,50,8.1,720
6,B,X,60,2.5,3650
7,B,X,70,2.6,3650
8,B,X,80,2.7,3650
13,B,X,130,2.8,720
14,B,W,140,3.9,720
15,B,X,150,9.6,180
16,B,Y,160,8.3,180
17,B,X,170,4.7,3650
;
run;

/*Required output:*/
/*in x dimension will have categories of :TEAM(X/Y/W)*/
/*in Y dimension will have categories of :Model(A/B) and under it categories of LenghtLoan(above 365 / below 365);*/
/*Need to calucltaed: N_records ,SUM OF SUMLOAN , weighted avarage of interest*/



 proc summary data=rawtbl(where=(Model='A' and LenghtLoan<365)) nway ;
    class Team;
    var interest/weight=SUMLOAN;
    var SUMLOAN;
    output out=ModelA_Less1Year (drop=_type_ _freq_)
           mean(interest)=Weighted_Avg_interest
           n(SUMLOAN)=n_loan 
           sum(SUMLOAN)=SUMLOAN;
run;

 proc summary data=rawtbl(where=(Model='A' and LenghtLoan>=365)) nway ;
    class Team;
    var interest/weight=SUMLOAN;
    var SUMLOAN;
    output out=ModelA_More1Year (drop=_type_ _freq_)
           mean(interest)=Weighted_Avg_interest
           n(SUMLOAN)=n_loan 
           sum(SUMLOAN)=SUMLOAN;
run;


 proc summary data=rawtbl(where=(Model='B' and LenghtLoan<365)) nway ;
    class Team;
    var interest/weight=SUMLOAN;
    var SUMLOAN;
    output out=ModelB_Less1Year (drop=_type_ _freq_)
           mean(interest)=Weighted_Avg_interest
           n(SUMLOAN)=n_loan 
           sum(SUMLOAN)=SUMLOAN;
run;

 proc summary data=rawtbl(where=(Model='B' and LenghtLoan>=365)) nway ;
    class Team;
    var interest/weight=SUMLOAN;
    var SUMLOAN;
    output out=ModelB_More1Year (drop=_type_ _freq_)
           mean(interest)=Weighted_Avg_interest
           n(SUMLOAN)=n_loan 
           sum(SUMLOAN)=SUMLOAN;
run;

Re: two dimensional report with 2 varaibles in y and one in X

ed_sas_member — Mon, 13 Jan 2020 09:08:45 GMT

/*UNTESTED CODE*/


proc format;
	value LenghtLoan
			low-365 = "Below 365"
			365-high = "Above 365";	
run;

proc tabulate data=rawtbl;
	class team model LenghtLoan;
	var SUMLOAN interest;
	table team, (model*LenghtLoan) * (n SUMLOAN*sum interest*mean) ;
	weight SUMLOAN;
	format LenghtLoan LenghtLoan.;
run;

Are you sure you want the sum of sumloan?

Re: two dimensional report with 2 varaibles in y and one in X

Ronein — Mon, 13 Jan 2020 10:01:01 GMT

Thank you. It is perfect.

Sum of SumLoan summarize the sum of loans .

I have some more questions please:

1-Replace Null values (dot symbol) with 0

2- Order of Rows by sum Of SumLoans for all models

3- Add another Raw with totals data information

4-Create calculations (3 columns) for Model A (All lenghts)

5-Create calculations (3 columns) for Model B (All lenghts)

6- Create calculations (3 columns) for Below365 (All Models)

7-Create calculations (3 columns) for Above365 (All Models)

8-Create calculations (3 columns) for All

So now the required table will be much wider

Re: two dimensional report with 2 varaibles in y and one in X

ed_sas_member — Mon, 13 Jan 2020 10:51:06 GMT

Hi @Ronein

You can use options missing='0'; as a global statement to put 0 instead of dots.

The ALL keyword in the table statement will allow you to display total in row / in columns according to the class variables (see. example below).

Regarding question #2, neither PROC TABULATE nor PROC REPORT will allow you to order on a statistic or calculated analysis variable. As far as I know, the only way to do this is to create an ordering variable, and then force PROC REPORT to show the data according to this variable.

Hope this help!

Best,


Options missing='0';
proc tabulate data=rawtbl;
	class team model LenghtLoan/order=sum;
	var SUMLOAN interest;
	table team all, ((model)*(LenghtLoan all)) * (n SUMLOAN*sum interest*mean);
	table team all, ((LenghtLoan)*(model all)) * (n SUMLOAN*sum interest*mean);
	weight SUMLOAN;
	format LenghtLoan LenghtLoan.;
run;