Help using Base SAS procedures

Why cannot I control row title headings of this table?

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

Why cannot I control row title headings of this table?

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


Accepted Solutions
Solution
‎10-01-2012 09:47 AM
Super User
Posts: 10,018

Re: Why cannot I control row title headings of this table?

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


All Replies
Solution
‎10-01-2012 09:47 AM
Super User
Posts: 10,018

Re: Why cannot I control row title headings of this table?

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

Super User
Posts: 11,343

Re: Why cannot I control row title headings of this table?

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.

Attachment
Super Contributor
Posts: 338

Re: Why cannot I control row title headings of this table?

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


🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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