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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

5 REPLIES 5
ballardw
Super User

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

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
Obsidian | Level 7

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

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
Obsidian | Level 7

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

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, 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
  • 5 replies
  • 1232 views
  • 6 likes
  • 3 in conversation