Hi Forum,
In the final table generated by this proc tabulate code, I need the row titles and column titles in this order:
Current, 1 - 30, 60 - 90
Question:
Column title order works but row title order does not.
Could any one help me?
data have;
informat current_date date9.;
input Bank_number Account_number $ 4-12 Current_date Product $ 24-36 Balance Arrears_Band $ 42-48 prior_arrears_band $ 50-56;
format current_date date9.;
cards;
10 444444444 31MAR2010 Personal Loan 800 60 - 90 1 - 30
40 777777 28MAR2010 Res. Mortgage 905 Current Current
70 666666666 15MAR2010 Personal OD 666 Current Current
;
run;
/*User defined format to control the column and row title orders*/
proc format ;
value $ fmt (multilabel notsorted)
'Current'='Current'
'1 - 30'='1 - 30'
'60 - 90'='60 - 90'
;
run;
proc tabulate data= have order=formatted;
class prior_arrears_band /PRELOADFMT order=data; /*row CLASS statement*/
class arrears_band /PRELOADFMT order=data; /*column CLASS statement*/
var balance;
table prior_arrears_band='', arrears_band*(n balance*sum)/ box=prior_arrears_band;
format arrears_band $fmt.; /*applying the earlier defined format*/
Title 'Succession of O/S Balance ($) and Accounts (#) from Feb 2010 to Mar 2010';
run;
Final Table:
prior_arrears_band | Arrears_Band | |||
Current | 60 - 90 | |||
N | Balance | N | Balance | |
Sum | Sum | |||
1 - 30 | . | . | 1 | 800 |
Current | 2 | 1571 | . | . |
Thanks
Mirisage
So did you try my code at another your post before ?
data have; informat current_date date9.; input Bank_number Account_number $ 4-12 Current_date Product $ 24-36 Balance Arrears_Band $ 42-48 prior_arrears_band $ 50-56; format current_date date9.; cards; 10 444444444 31MAR2010 Personal Loan 800 60 - 90 1 - 30 40 777777 28MAR2010 Res. Mortgage 905 Current Current 70 666666666 15MAR2010 Personal OD 666 Current Current ; run; proc format ; value $ fmt(default=20) 'Current'=' Current' '1 - 30'=' 1 - 30' '30 - 60'=' 30 - 60' '60 - 90'=' 60 - 90' 'NPNA'='NPNA' ; run; proc tabulate data= have order=formatted; class prior_arrears_band arrears_band; /*column CLASS statement*/ var balance; table prior_arrears_band='', arrears_band*(n balance*sum)/ box=prior_arrears_band; format prior_arrears_band arrears_band $fmt.; /*applying the earlier defined format*/ Title 'Succession of O/S Balance ($) and Accounts (#) from Feb 2010 to Mar 2010'; run;
Ksharp
So did you try my code at another your post before ?
data have; informat current_date date9.; input Bank_number Account_number $ 4-12 Current_date Product $ 24-36 Balance Arrears_Band $ 42-48 prior_arrears_band $ 50-56; format current_date date9.; cards; 10 444444444 31MAR2010 Personal Loan 800 60 - 90 1 - 30 40 777777 28MAR2010 Res. Mortgage 905 Current Current 70 666666666 15MAR2010 Personal OD 666 Current Current ; run; proc format ; value $ fmt(default=20) 'Current'=' Current' '1 - 30'=' 1 - 30' '30 - 60'=' 30 - 60' '60 - 90'=' 60 - 90' 'NPNA'='NPNA' ; run; proc tabulate data= have order=formatted; class prior_arrears_band arrears_band; /*column CLASS statement*/ var balance; table prior_arrears_band='', arrears_band*(n balance*sum)/ box=prior_arrears_band; format prior_arrears_band arrears_band $fmt.; /*applying the earlier defined format*/ Title 'Succession of O/S Balance ($) and Accounts (#) from Feb 2010 to Mar 2010'; run;
Ksharp
Your class statements using ORDER=DATA say to display data in the order that it appears within the input data set.
Any particular reason for using a multilabel format? They can behave differently depending on options.
I have attached a file that shows some interactions between multilabel formats and different data=options.
Hi Ksharp and ballardw,
Thank you very much to both of you.
Hi Ksharp,
This code worked really well. Great relief.
(Actually I have tried your example code provided in another posting too although I failed in that attmept).
Hi ballardw,
Thank you very much for your attachement too.
Regards
Mirisage
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.