DATA Step, Macro, Functions and more

PROC TABULATE Order

Accepted Solution Solved
Reply
anonymous_user
Posts: 0
Accepted Solution

PROC TABULATE Order

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;


Accepted Solutions
Solution
Accepted by anonymous_user
‎08-17-2016 05:55 AM
SAS Super FREQ
Posts: 708

Re: PROC TABULATE Order

Posted in reply to anonymous_user

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

View solution in original post


All Replies
Solution
Accepted by anonymous_user
‎08-17-2016 05:55 AM
SAS Super FREQ
Posts: 708

Re: PROC TABULATE Order

Posted in reply to anonymous_user

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

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 1280 views
  • 3 likes
  • 2 in conversation