topic Re: Proc Tabulate to mirror pivot table in SAS Procedures

Proc Tabulate to mirror pivot table

SYLC — Fri, 07 Oct 2022 17:35:58 GMT

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.

Re: Proc Tabulate to mirror pivot table

ballardw — Fri, 07 Oct 2022 19:05:10 GMT

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.

Re: Proc Tabulate to mirror pivot table

Reeza — Fri, 07 Oct 2022 20:11:40 GMT

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

Re: Proc Tabulate to mirror pivot table

SYLC — Mon, 10 Oct 2022 09:51:16 GMT

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.

Re: Proc Tabulate to mirror pivot table

Reeza — Mon, 10 Oct 2022 18:26:28 GMT

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

Re: Proc Tabulate to mirror pivot table

ghosh — Tue, 11 Oct 2022 20:47:16 GMT

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;

Re: Proc Tabulate to mirror pivot table

Ksharp — Wed, 12 Oct 2022 11:42:20 GMT

/*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;