Proc Tabulate to mirror pivot table

[SYLC]

Hello.

I have create this code:

proc tabulate data=sashelp.cars;
class Origin DriveTrain;
table Origin * (DriveTrain all="Sub Total") all="Grand Total", all*n;
keylabel n='Number of each Drive Train';
run;

Which gives me this:

But how do I sort the all, front and rear in descending order for each drivetrain like it does in excel pivot table below:

Thanks in advance.

[ballardw]

Try this:

proc tabulate data=sashelp.cars;
   class Origin;
   class DriveTrain/ order=freq;
   table Origin * (all="Sub Total" DriveTrain ) all="Grand Total", all*n;
   keylabel n='Number of each Drive Train';
run;

I moved the All for the Drivetrain subtotal to have the total appear a the top if you want that behavior from the "pivot" as well.

Caution: the order=freq can get tricky with nested variables in complex tables.

---

@SYLC wrote:

Hello.

I have create this code:

proc tabulate data=sashelp.cars;
class Origin DriveTrain;
table Origin * (DriveTrain all="Sub Total") all="Grand Total", all*n;
keylabel n='Number of each Drive Train';
run;

Which gives me this:

But how do I sort the all, front and rear in descending order for each drivetrain like it does in excel pivot table below:

Thanks in advance.

---

[SYLC]

Thanks. I see what you mean by tricky. For the most part it has work but there are a few rows out of sequence.

I guess the alternative would be to create a table from scratch using the count function.

[Reeza]

Another alternative is to pipe the output to a table, sort using PROC SORT and then display using PROC REPORT.

[Reeza]

FYI - you should probably remove all from your pivot table as it double counts if you're using that Excel for anything.

[ghosh]

proc tabulate data=sashelp.cars;
class Origin DriveTrain /order=freq;
table Origin * (DriveTrain all="Sub Total") all="Grand Total" , all=''*n 
  / Box='Origin and Drive Train';
keylabel n='Number of each Drive Train';
run;

[Ksharp]

/*Very interesting question.
Hope SAS could have an option to handle this sitation.*/
proc freq data=sashelp.cars noprint ;
table Origin*DriveTrain/out=classdata ;
run;
proc sort data=classdata;by Origin descending count;run;
data classdata;
length DriveTrain $ 100;
 set classdata;
 by Origin;
n+first.Origin;
DriveTrain=repeat(' ',n)||DriveTrain;
drop n;
run;



proc tabulate data=classdata  format=best. ;
class Origin DriveTrain/order=data;
var count;
table Origin * (DriveTrain all="Sub Total") all="Grand Total", all*count="Number of each Drive Train"*sum="";
run;