How can I create pivot tables in SAS?

---

You can use PROC TABULATE in SAS to create pivot tables to summarize variables in a dataset.

This procedure uses the following basic syntax:

proc tabulate data=my_data;
    class var1;
    var var2 var3;
    table var1, var2 var3;
run;

The class statement specifies the variable to group by, the var statement specifies the numeric variables to summarize, and the table statement specifies the format of the pivot table.

The following example shows how to use this syntax in practice.

Example: Use Proc Tabulate to Create a Pivot Table in SAS

Suppose we have the following dataset in SAS that contains information about the number of sales and returns made at various grocery stores:

/*create dataset*/
data my_data;
    input store $ sales returns;
    datalines;
A 10 2
A 7 0
A 7 1
A 8 1
A 6 0
B 10 2
B 14 5
B 13 4
B 9 0
B 5 2
C 12 1
C 10 1
C 10 3
C 12 4
C 9 1
;
run;

/*view dataset*/
proc print data=my_data;

Now suppose that we would like to create a pivot table that summarizes the sum of sales and returns at each store.

We can use the following syntax to do so:

/*create pivot table to summarize sum of sales and returns by store*/
proc tabulate data=my_data;
    class store;
    var sales returns;
    table store, sales returns;
run;

The resulting pivot table shows the sum of sales and returns at each store.

For example, we can see:

• The sum of sales made at store A is 38.
• The sum of returns made at store A is 4.
• The sum of sales made at store B is 51.
• The sum of returns made at store B is 13.

And so on.

However you can type *Mean after each numeric variable to instead calculate the mean value:

/*create pivot table to summarize mean of sales and returns by store*/
proc tabulate data=my_data;
    class store;
    var sales returns;
    table store, sales*Mean returns*Mean;
run;

The resulting pivot table shows the mean of sales and returns at each store.

For example, we can see:

• The mean value of sales made at store A is 7.6.
• The mean value of returns made at store A is 0.80.
• The mean value of sales made at store B is 10.2.
• The mean value of returns made at store B is 2.6.

And so on.

The following tutorials explain how to perform other common tasks in SAS: