Proc tabulate problem?

[Mirisage]

Hi SAS Forum,

I wonder if you could help me on this please.

I wanted to produce a table using the attahced data set.

I have used the follwing code and generated a table close to what I wanted but needs some changes in the order of headings.

proc tabulate data=a.book3;

  class prior_arrears_band;

  class arrears_band;

  var balance;

  table prior_arrears_band, arrears_band*(N balance*SUM);

run;

This is the table produced.

	Arrears_Band
	30 - 60		60 - 90		Current		NPNA
	N	Balance	N	Balance	N	Balance	N	Balance
		Sum		Sum		Sum		Sum
prior_arrears_band	.	.	1	800	.	.	.	.
1 - 30
Current	1	555	.	.	2	1571	.	.
NPNA	.	.	.	.	.	.	1	700

Question:

i) In the horizontal headings (colored in red) I need the order like this: Current, 30 - 60, 60-90, NPNA

ii) In the vertical headings (colred in blue) I need the order: Current, 1 -30, NPNA

iii). The heading prior_arrears_band has to be lying vertically down at the very left extreme of the table (becuase this corss tab is between

prior_arrears_band and Arrears_Band).

Could anyone please help me to tweak the proc tabulate code?

Thanks

Mirisage

[ballardw]

i) In the horizontal headings (colored in red) I need the order like this: Current, 30 - 60, 60-90, NPNA

ii) In the vertical headings (colred in blue) I need the order: Current, 1 -30, NPNA

The most consistent approach I've found for odd ordering is to create a new numeric variable and create a custom format with values.

iii). The heading prior_arrears_band has to be lying vertically down at the very left extreme of the table

I don't think it is possible to get vertical text the way you are thinking with tabulate

You can generate label to the left using a different  variable, for exam prior_label whose value or format is always prior_arrears_band, though I would make it more language in appearance than variable name.

Then add the prior_label variable to the class statement and table statement would look something like:

table prior_label=''*prior_arrears_band='', arrears_band*(N balance*SUM);

The ='' suppresses the label or variable name from appearing for that variable in the table output.

Alternatively use prior_arrears_band='', arrears_band*(n balance*sum)/ box=prior_arrears_band; to have that label as the column heading instead of as it appears currently.

[Ksharp]

Give you an example .

data x;
input a $ b $ x;
cards;
NPNA   30-60   1
Current  30-60   1
1-30   60-90  1
NPNA   NPNA   1 
1-30   Current   1 
;
run;
proc format ;
value $ fmt(default=20)
  'Current'='     Current'
  '1-30'='   1-30'
  '30-60'='  30-60'
  '60-90'=' 60-90'
  'NPNA'='NPNA'
  ;
run;
proc tabulate data=x order=formatted;
class a b;
format a b $fmt.;
var x;
table a,b*x;
run;

Ksharp

[Mirisage]

Hi Ballardw and Ksharp,

Many thanks to both of you.

Hi Ballardw

I appplied your second suggestion (highlighted in yellow color in the following code). It works. thank you.

Hi Ksharp,

Many thanks for taking time to provide an example.

I applied your "proc format" suggestion (please see belwo). But still the order of catelgorical columns are not coming in the order defined in proc format. Is there any problem in my proc format code or proc tabulate code? Also i saw you have intentionally leaft some space '1-30'='   1-30'.

/*Format $ fmt. will be used in tabulate and will control the order in which the categorical

   columns are laid out*/

proc format ;

value $ fmt(multilabel notsorted) /*See note below */

  'Current'='Current'

  '1-30'='1-30'

  '30-60'='30-60'

  '60-90'='60-90'

  'NPNA'='NPNA'

    ;

run;

proc tabulate data=book3 order=formatted;

  class prior_arrears_band;

  class arrears_band /PRELOADFMT order=data;

   var balance;

table prior_arrears_band='', arrears_band*(n balance*sum)/ box=prior_arrears_band;

format arrears_band $fmt.;

run;

/*Note: from literature:

"Notsorted" option maintains the sort order as specified in the format and can be used in conjunction with the multilabel format option

*/

[ballardw · Accepted solution]

Try

'1-30' = ' 1-30'  /* add a space as <space>1 will usually sort before 30.

for your example. One reason I recommended a numeric variable was to avoid using the formatted value as the formatted values are going to get sorted using your systems collating sequence.

And the "notsorted" option seems to be problematic for maintaining order depending on procedure and options used.

[Mirisage]

Hi ballardw,

The reason why the catelgorical columns are not coming in the order defined in proc format is becasue I have not defined them as exactly as appeard in the data set values (Thanks to Joe Matise for spotting this).

Incorrect

'1-30'='1-30' 

correct (see the space up)

'1 - 30'='1-30' 

Thanks

Mirisage