## Ordering the values in a cross tab output?

Solved
Super Contributor
Posts: 338

# Ordering the values in a cross tab output?

Hi SAS Community,

I have run the below proc freq.

proc freq data=QP.have;

tables Days_Past * arrears_band/missing nocol norow nopct ;

where product IN ('OD','Loan','Mort');

run;

This is the output.

Table of Days_Past  by Arrears_Band

 Arrears_Band Total 1 - 30 30 - 60 60 - 90 90 + Current NPNA Days_Past 0 0 0 0 48 69 ÿ Frequency 0 Frequency 0 0 0 0 797 5 1 Frequency 300 0 0 0 0 613 10 Frequency 504 0 0 0 0 25 100 Frequency 0 0 0 1 0 107 101 Frequency 0 0 0 0 0 61 102 Frequency 0 0 0 0 0 103 103 Frequency 0 0 0 6 0 78 104 Frequency 0 0 0 1 0 37 105 Frequency 0 0 0 0 0 82 106 Frequency 0 0 0 1 0 78 107 Frequency 0 0 0 1 0 141 108 Frequency 0 0 0 1 0 103 109 Frequency 0 0 0 1 0 36 11 Frequency 165 0 0 0 0 30

Question:

In the above Table:

i)                    I need to get the values of the “Days_Past” column in ascending order

ii)                  Need to replace notation ÿ with the word missing or ‘ ’

iii)                Need to get rid of the repeating word "Freequency"

I have tried with DATA, FORMATTED, and INTERNAL options in place of yellow-higlighed “freq” below but non works.

proc freq data=QP.have order=freq;

tables Days_Past * arrears_band/missing nocol norow nopct ;

where product IN ('OD','Loan','Mort');

run;

/*This excellent example provided by Ksharp also cannot be applied because I have about 700 values for the variable “Days_Past” */

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;

Could anyone help me on this?

Thanks

Mirisage

Accepted Solutions
Solution
‎11-07-2012 11:27 AM
PROC Star
Posts: 8,164

## Re: Ordering the values in a cross tab output?

Wouldn't something like the following give you what you want?:

data need;

set have (rename=(Days_Past=_Days_Past));

Days_Past=input(_Days_Past, ?? 12.);

run;

proc freq data=need order=internal;

tables Days_Past * arrears_band/missing nocol norow nopct ;

where product IN ('OD','Loan','Mort');

run;

All Replies
Super User
Posts: 23,724

## Re: Ordering the values in a cross tab output?

Not to be a PITA but haven't you asked multiple questions like this before?

Basically the BEST way to do it, is not to code to character values like you have but use numeric codes.

Ie rather than

.=' Missing'

0 = 'Current'

1= '1 - 30'

2= '30 - 60'

3='60 - 90'

4 ='90+'

5=  'Default'

6= ' Delinquent'

7= 'NPNA'

Then apply the format using a proc tabulate where you can control all the labels in the table.

With this method you don't need extra spaces in the labels which can mess up your reports and the sorts will work fine.

Super Contributor
Posts: 338

## Re: Ordering the values in a cross tab output?

Hi Reeza,

Thanks.

This question is actually different than the several questions I have asked previously.

I wanted to attach my data set into this to make my question clearer.

However, I cannot attach it into this.

So, I have to do new posting and please have a look on it if you have time.

Thanks

M

Super User
Posts: 13,542

## Re: Ordering the values in a cross tab output?

Days_past is sorting in character order. The variable is probabably not numeric and is sorting properly if that is the case.

You may have modified the template that Proc Freq is using to do crosstabs, possibly from running an example somewhere as the label FREQUENCY normally will not appear in the output.

ods path sashelp.tmplmst(read) sasuser.templat(update);

before the proc freq to reset the search order of templates to use the default templates and see if that will fix the "Frequency" text problem.

Solution
‎11-07-2012 11:27 AM
PROC Star
Posts: 8,164

## Re: Ordering the values in a cross tab output?

Wouldn't something like the following give you what you want?:

data need;

set have (rename=(Days_Past=_Days_Past));

Days_Past=input(_Days_Past, ?? 12.);

run;

proc freq data=need order=internal;

tables Days_Past * arrears_band/missing nocol norow nopct ;

where product IN ('OD','Loan','Mort');

run;

Super Contributor
Posts: 338

## Re: Ordering the values in a cross tab output?

Hi ballardw and Art,

Many thanks.

Hi Art,

What you have suggested works. Thanks.

I would like to ask your help to learn another aspect from your code.

data need;

set have (rename=(Days_Past=_Days_Past));

Days_Past=input(_Days_Past, ?? 12.);

run;

Above code performs sequentially what I have shown below:

1). It takes the character variable named Days_Past from work.have and renames it to _Days_Past

2). Then the _Days_Past variable is converted into a numeric variable and gives the name Days_Past

double question mark (??) modifier suppresses the printing of both the error messages and the input lines when invalid data values are read.

Am I correct?

Question:

How come then the following code does the job differently (I have explained the sequence after the below code).

data work.want            (rename =  (N_VAR1= VAR1

N_VAR2 =   VAR2

N_VAR3 = VAR3));

set work.have ;

N_VAR1 = input(VAR1, 8.);

N_VAR2 = input (VAR2,8.);

N_VAR3 = input (VAR3, 8.);

drop VAR1

VAR2

VAR3

;

run;

1). First, it takes 3 character variables named VAR1, VAR2 and VAR3 from work.have dataset and then converts each one of them into numeric variables giving names N_VAR1, N_VAR2 and N_VAR3 respectively.

Whereas in your code, renaming was done first. How in the second code variable conversion happend first?

2). Then drops 3 character variables named VAR1, VAR2 and VAR3

3). Then it gets 3 numeric variables named N_VAR1, N_VAR2 and N_VAR3, and then renames them back into VAR1, VAR2 and VAR3

Is this correct?

Could you please help me to understand this differential behavior of seemingly same two codes.

Thanks

M.

PROC Star
Posts: 8,164

## Re: Ordering the values in a cross tab output?

The two sets of code appear to be doing mostly the same thing and, yes, your explanations appear to be correct.  Are you obtaining different results from the two methods?

The principal differences I see between the two sets of code are that:

1. My code will silently set alpha characters to missing during the conversion

2. You drop the original variables while my code didn't (but easily could have)

As for whether one renames or drops in the set statement, the data statement or in the code itself, is probably just a matter of one's personal preference.  I happen to like doing those things in the data and set statements as, there, I know the order in which the statements will be executed, namely alphabetically.  Drops are done before Keeps which are done before Renames.  Honestly, I've never even considered if there is such a precedence inherent when they are included as separate statements in the datastep.

Super Contributor
Posts: 338

## Re: Ordering the values in a cross tab output?

Hi Art,

Thank you very much for this knowledge, i.e. "Alphabetical execution of "Drop", "Keep" "Rename" statements".

Regards

Mirisage

Super User
Posts: 6,774

## Re: Ordering the values in a cross tab output?

Mirisage,

Your last example doesn't work because you moved the RENAME from the SET to the DATA statement.  It belongs on the SET statement (not as a matter of syntax, but as a matter of logic for this particular application).

Good luck.

🔒 This topic is solved and locked.

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

Discussion stats
• 8 replies
• 801 views
• 6 likes
• 5 in conversation