PROC TABULATE Order

[#1]

Hi, 

I have my PROC TABLULATE below, but within the output, I would like to format the order. 

This is how I would like it - 

Indexed LTV	#
	BTL			Resi
	IO (inc End & Lifetime)	Repayment	Part and Part	IO (inc End & Lifetime)	Repayment	Part and Part

But my output is, how do I make it so it matches the order above please within my PROC TABULATE - 

Indexed LTV	#
	RES			BTL
	IO (inc End & Lifetime)	Part and Part	Repayment	IO (inc End & Lifetime)	Part and Part	Repayment

proc tabulate data = mortbib.mortgagebible_201607 missing;
class curr_ind_ltv prod_class repay_type/preloadfmt order=data mlf;
var bal_sasout;
table (curr_ind_ltv="" all="Total"),
(n="#" sum="£"*f=comma19. ) *
(prod_class="") *
(repay_type="") *
(bal_sasout="") /box="Indexed LTV";
format curr_ind_ltv ltv. prod_class $pclass. repay_type $repay.;
run;

[BrunoMueller · Accepted solution]

Hi

I have made good experience with using the CLASSDATA= option in Proc TABULATE. It allows you to specify an order as you need it for your class variable values. See the example below.

/*
 * sample data
 */
data carsSubset;
  set sashelp.cars;
  where make in (
    "Volkswagen", "Audi", "Volvo"
    , "Toyota", "Honda", "Subaru"
    , "Chrysler", "Ford", "Pontiac"
  );
  random = ceil( ranuni(1234) * 500 );
  keep origin type make model driveTrain invoice horsePower mpg_: random;
run;

/*
 * sort data in some random order
 */
proc sort data=carsSubset;
  by random;
run;

/*
 * create structure data, in the sequence as you would like to see them in the report
 * all CLASS variables must be present, but not all values
 */
data carsClassData;
  infile cards dsd dlm=",";
  input
    make : $13.
    driveTrain : $5.
  ;
cards;
Toyota,Front
Toyota,All
Toyota,Rear
;

title "1) carsClassData";
proc print data=carsClassData;
run;

title "2) Applied to tabulate";
proc tabulate
  data=work.carssubset
  classdata=carsClassData
  /* only use the class values present in the CLASSDATA= */
/*  exclusive*/
  order=data
  format=nlnum12.

;
  class make  /  order=data  ;
  class DriveTrain  / order=data ;
  var invoice;

  table
    make
    , driveTrain * invoice*(n min max mean)
  ;
run;
title;

Bruno