Controlling the order of column & row titles in proc format?

[Mirisage]

Hi Forum,

I ran a cross tab like below.

proc freq data=a.have ;

tables delinquency_band*arrears_band/ missing nocol nopercent norow;

run;

It outputted a table like below.

Table of Delinquency_Band by Arrears_Band
		Arrears_Band						Total
		1 - 30	30 - 60	60 - 90	90 +	Current	NPNA
Delinquency_Band		104251	0	0	0	54782	0	????
Current	Frequency
Default	Frequency	0	0	0	1297	0	0	1297
Delinquen	Frequency	0	25487	0	0	0	325
NPNA	Frequency	0	0	0	0	0	25078	25078
		????	????	????	????	????	????

Question:

I want to revise the row and column title order of the above table like this.

Row order (i.e. Arrears_Band order)

Missing      Current        1 – 30              30 – 60              60 – 90          90+             NPNA

Column order (i.e. Delinquency_Band order)

Missing     Current         Delinquent               Default     NPNA

What I have done:

/* create format for sort order of Arrears_Band  */

proc format ;

  invalue Arrears_Band

    'CURRENT' =1

    '1-30'    =2

    '30-60'   =3

    '60-90'   =4

    '90+'     =5

    'NPNA'    =6

     OTHER     =99

  ;

  value ArrearsText

    1='Current'

    2='1 - 30'

    3='30 - 60'

    4='60 - 90'

    5='90 +'

    6='NPNA'

    99='missing and misscoded!'

  ;

run;

proc freq data=a.ARR_HISTRY_FILTRD_HAPHAZ_RMVD;

tables delinquency_band*arrears_band/ missing nocol nopercent norow;

format ArrearsText.;

run;

This doesn’t work.

Could anyone let me know what is the problem with my code?

Thanks

Mirisage

[Tom]

The syntax of your FORMAT statement is wrong.  Should have a variable name followed by optional format name.

Also I did not see anything in the posted code to see where you used to INFORMAT to convert the values of Arrears_Band from text strings to numbers. (Note you cannot convert and existing variable from character to number, you will need to create a new one. You can use RENAME statements to change the name back if you want.)

If your Arrears_Band variable actually has the values 1,2,...,99 try running the PROC FREQ with the format statement:

format arrears_band ;

This should give you column headings of 1,2,3,... etc.

To get missing to appear first you might want to recode it from 99 to 0.

[Ksharp · Accepted solution]

So did you try the code I told you a couple of days ago ?

data x;
input a & $10. b & $10.;
cards;
.        .                                      
Current   Current
1 - 30     Delinquent
30 - 60     Default
60 - 90     NPNA
90+         Current 
NPNA        Current
;
run;
proc format ;
  value $ fmt
    ' '='           Missing'
    'Current' = '      Current'
    '1 - 30'    = '     1 - 30' 
    '30 - 60'   = '    30 - 60'
    '60 - 90'   ='   60 - 90' 
    '90+'     ='  90+' 
    'Default'=  'Default'
    'Delinquent'= ' Delinquent'
    'NPNA'    = 'NPNA' 
    ;
run;
proc freq order=formatted;
tables b*a/missing ;
format a b $fmt.;
run;

Ksharp