Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

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

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_BandTotal
1 - 3030 - 6060 - 9090 +CurrentNPNA
Delinquency_Band 104251000547820????
CurrentFrequency
DefaultFrequency0001297001297
DelinquenFrequency025487000325
NPNAFrequency000002507825078
????????????????????????

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


Accepted Solutions
Solution
‎10-10-2012 10:13 PM
Super User
Posts: 9,671

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

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

View solution in original post


All Replies
Super User
Super User
Posts: 6,499

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

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.

Solution
‎10-10-2012 10:13 PM
Super User
Posts: 9,671

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

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

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 195 views
  • 3 likes
  • 3 in conversation