proc tabulate

[Ronein]

Hello

Please find a raw data table .

I am sorry that I cannot attach excel file to show the output table that I want.

The required output table will contain 5 rows and 12 columns.

Col1 header will be "statistics" and it will have values:

No_Loans

Sum_Loans

Sum_Profit

PCT_Profit (It is calculated by Sum_Profit/Sum_Loans)

Col2-Col11 headers will be :

Q1-2017   Q1-2018   Q2-2017   Q2-2018   Q3-2017   Q3-2018   Q4-2017   Q4-2018   Total 2017   Total 2018

How can I create the desired output with proc tabulate?

Data rawdata;
input ID quarter_year$	year	Sum_Loan  sum_profit;
cards;
1	Q1-2017	2017	10 1
2	Q1-2017	2017	20 2
3	Q1-2018	2018	30 3
4	Q1-2018	2018	40 4
5	Q1-2018	2018	50 5
6	Q2-2017	2017	60 6
7	Q2-2018	2018	70 7
8	Q2-2018	2018	80 8
9	Q3-2017	2017	90 9
10	Q3-2017	2017	100 1
11	Q3-2017	2017	110 2
12	Q3-2018	2018	120 3
13	Q3-2018	2018	130 4
14	Q4-2017	2017	140 5
15	Q4-2017	2017	150 6
16	Q4-2017	2017	160 7
17	Q4-2017	2017	170 8
18	Q4-2018	2018	180 9
19	Q4-2018	2018	190 10
20	Q4-2018	2018	200 11
;
run;

[Ronein]

This code is not giving me the required output because:

I want to have one output table and not multiple.

I want also to calculate  PCT_profit that is calculated by sum_profit/sum_loans

I want also to calculate Total_2017  and Total_2018

PROC TABULATE DATA=rawdata;
class quarter_year;
	VAR Sum_Loan sum_profit ;
	TABLE  (Sum N)*Sum_Loan,quarter_year  ;
    TABLE       sum*sum_profit,quarter_year;
RUN;

[Ronein]

There is some progress but still: 

need to calculate  PCT_profit that is calculated by sum_profit/sum_loans

and also to calculate Total_2017  and Total_2018

PROC TABULATE DATA=rawdata;
class quarter_year;
	VAR Sum_Loan sum_profit ;
	TABLE  (Sum N)*Sum_Loan  sum*sum_profit,quarter_year  ;
RUN;

[Ronein]

I did this step but now need to transpose it.

title;
PROC TABULATE DATA=rawdata   out=b;
class quarter_year;
	VAR Sum_Loan sum_profit ;
	TABLE  (Sum='sum_Loans' N='No_Loans')*Sum_Loan=''
            sum='Sum_profit'*sum_profit='',quarter_year  ;
RUN;

Data c;
set b;
PCT_profit=Sum_profit_sum/Sum_Loan_Sum;
format PCT_profit percent9.2;
Drop _type_ _page_ _table_;
rename Sum_Loan_N=No_loans Sum_profit_sum=Sum_profit  Sum_Loan_Sum=Sum_Loan;
Run;

[Ronein · Accepted solution]

I found part solution.

My question is how to add total_2017 and total_2018.

and also to ask if there is a way to do it all in one step

title;
PROC TABULATE DATA=rawdata  out=b;
class quarter_year;
	VAR Sum_Loan sum_profit ;
	TABLE  (Sum='sum_Loans' N='No_Loans')*Sum_Loan=''
            sum='Sum_profit'*sum_profit='',quarter_year  ;
RUN;

Data c;
set b;
PCT_profit=Sum_profit_sum/Sum_Loan_Sum;
format PCT_profit percent9.2;
Drop _type_ _page_ _table_;
rename Sum_Loan_N=No_loans Sum_profit_sum=Sum_profit  Sum_Loan_Sum=Sum_Loan;
Run;


proc transpose data=c out=cc
name=Col 
prefix=_; 
id quarter_year;
run;

[ballardw]

Consider:

Data rawdata;
input ID quarter_year$	year	Sum_Loan  sum_profit;
cards;
1	Q1-2017	2017	10 1
2	Q1-2017	2017	20 2
3	Q1-2018	2018	30 3
4	Q1-2018	2018	40 4
5	Q1-2018	2018	50 5
6	Q2-2017	2017	60 6
7	Q2-2018	2018	70 7
8	Q2-2018	2018	80 8
9	Q3-2017	2017	90 9
10	Q3-2017	2017	100 1
11	Q3-2017	2017	110 2
12	Q3-2018	2018	120 3
13	Q3-2018	2018	130 4
14	Q4-2017	2017	140 5
15	Q4-2017	2017	150 6
16	Q4-2017	2017	160 7
17	Q4-2017	2017	170 8
18	Q4-2018	2018	180 9
19	Q4-2018	2018	190 10
20	Q4-2018	2018	200 11
;
run;

proc format library=work;
value $qy (multilabel notsorted)
'Q1-2017'='Q1-2017'
'Q2-2017'='Q2-2017'
'Q3-2017'='Q3-2017'
'Q4-2017'='Q4-2017'
'Q1-2018'='Q1-2018'
'Q2-2018'='Q2-2018'
'Q3-2018'='Q3-2018'
'Q4-2018'='Q4-2018'
'Q1-2017', 'Q1-2017','Q1-2017','Q1-2017'='Total 2017'
'Q1-2018', 'Q1-2018','Q1-2018','Q1-2018'='Total 2018'
;
run;
proc tabulate data=rawdata;
   class quarter_year /mlf;
   format quarter_year $qy.;
   var Sum_Loan  sum_profit;
   table sum_loan=''*(n='Number of loans' sum='Sum of loans'*f=best8.)
         sum_profit=''*(sum="Sum Profit"*f=best8. pctsum<sum_loan>='Pct Profit'),
         quarter_year=''
         /row=float box='Statistic'
   ;
run;

Proc tabulate is one of the procedures that will use multilabel formats. So careful design of the format can create custom groups of summaries. The multilabel formats are very sensitive to order of statements in the definition and the order the output appears.

Use <> to indicate a denominator for PCTSUM or PCTN statistics.

I have to say that it would make more sense to me to have an annual total at the end of the given year but this matches your apparent layout desire.

[Ronein]

Thank you so much,

I have learned a lot from this solution.

I just want to ask you about the calculation of PCT_profit that is calculated by sum_profit/sum of loans.

You wrote " sum_profit=''*(sum="Sum Profit"*f=best8. pctsum<sum_loan>='Pct Profit'),"

Is it telling SAS to calculate  : sum_profit/sum_loan  and then take % of that?

I have never seen this type of writting with <> symbols

[Ronein]

One more question please.

you wrote "row=float".

What is it doing?

Is it essential?

How is it helping us...?