BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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_bandArrears_Band
Current60 - 90
NBalanceNBalance
SumSum
1 - 30..1800
Current21571..

Thanks

Mirisage

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

3 REPLIES 3
Ksharp
Super User

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

ballardw
Super User

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.

Mirisage
Obsidian | Level 7

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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 724 views
  • 3 likes
  • 3 in conversation