topic Re: Ordering the values in a cross tab output? in SAS Procedures

Ordering the values in a cross tab output?

Mirisage — Fri, 02 Nov 2012 20:29:46 GMT

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	Total
Days_Past		0	0	0	0	48	69
ÿ	Frequency	0	0	0	0	48	69
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

Re: Ordering the values in a cross tab output?

Reeza — Fri, 02 Nov 2012 21:36:28 GMT

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.

Re: Ordering the values in a cross tab output?

Mirisage — Wed, 07 Nov 2012 03:16:18 GMT

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

Re: Ordering the values in a cross tab output?

ballardw — Wed, 07 Nov 2012 15:58:04 GMT

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.

Try adding:

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.

Re: Ordering the values in a cross tab output?

art297 — Wed, 07 Nov 2012 16:27:47 GMT

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;

Re: Ordering the values in a cross tab output?

Mirisage — Fri, 09 Nov 2012 14:20:49 GMT

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

Re: Ordering the values in a cross tab output?

art297 — Fri, 09 Nov 2012 14:47:12 GMT

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.

Re: Ordering the values in a cross tab output?

Mirisage — Mon, 12 Nov 2012 18:31:07 GMT

Hi Art,

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

Regards

Mirisage

Re: Ordering the values in a cross tab output?

Astounding — Mon, 12 Nov 2012 18:38:00 GMT

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.