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,

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

2 REPLIES 2
Tom
Super User Tom
Super User

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
Super User

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

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
  • 2 replies
  • 814 views
  • 3 likes
  • 3 in conversation