Help using Base SAS procedures

Proc tabulate problem?

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

Proc tabulate problem?

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 - 6060 - 90CurrentNPNA
NBalanceNBalanceNBalanceNBalance
SumSumSumSum
prior_arrears_band..1800....
1 - 30
Current1555..21571..
NPNA......1700

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

Attachment

Accepted Solutions
Solution
‎09-27-2012 12:48 PM
Super User
Posts: 10,500

Re: Proc tabulate problem?

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.

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Proc tabulate problem?

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.

Super User
Posts: 9,681

Re: Proc tabulate problem?

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

Super Contributor
Posts: 338

Re: Proc tabulate problem?

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

*/

Solution
‎09-27-2012 12:48 PM
Super User
Posts: 10,500

Re: Proc tabulate problem?

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.

Super Contributor
Posts: 338

Re: Proc tabulate problem?

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

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 311 views
  • 6 likes
  • 3 in conversation