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;
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;
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;
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;
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;
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;
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.
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
One more question please.
you wrote "row=float".
What is it doing?
Is it essential?
How is it helping us...?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.