topic Re: Controlling the order of column & row titles in proc format? in SAS Procedures

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

Mirisage — Wed, 10 Oct 2012 15:08:45 GMT

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	Total
Delinquency_Band		104251	0	0	0	54782	0	????
Current	Frequency	104251	0	0	0	54782	0	????
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

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

Tom — Wed, 10 Oct 2012 16:13:21 GMT

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.

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

Ksharp — Thu, 11 Oct 2012 02:13:56 GMT

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