turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Cross-tabulate the average of a third variable

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-02-2010 11:33 PM

Hi all,

Please do you know how to create the following cross-tab?

Suppose I want to summarize the average number of sales for three groups (High propensity to buy, Medium propensity to buy, Low propensity to buy) of people during different weeks.

Low Medium High

Week 1 2.5 3.5 2.2

Week 2 3.3 2.4 3.2

Week 3 2.1 3.1 2.7

(Suppose the three column names are WEEK, PROPENSITY, SALES)

If I use PROC FREQ data=work.dataset;

weight sales;

table week*propensity / norow nocol nopercent;

run;

I only get a summary of the "total number of sales" instead of the "average number of sales" I need.

I can produce crosstab of the average of a third variable via Excel pivot table easily, but somehow couldn't figure out how to do it via SAS.

Many thanks in advance.

Regards

silverflute

Please do you know how to create the following cross-tab?

Suppose I want to summarize the average number of sales for three groups (High propensity to buy, Medium propensity to buy, Low propensity to buy) of people during different weeks.

Low Medium High

Week 1 2.5 3.5 2.2

Week 2 3.3 2.4 3.2

Week 3 2.1 3.1 2.7

(Suppose the three column names are WEEK, PROPENSITY, SALES)

If I use PROC FREQ data=work.dataset;

weight sales;

table week*propensity / norow nocol nopercent;

run;

I only get a summary of the "total number of sales" instead of the "average number of sales" I need.

I can produce crosstab of the average of a third variable via Excel pivot table easily, but somehow couldn't figure out how to do it via SAS.

Many thanks in advance.

Regards

silverflute

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-03-2010 12:35 PM

I am confused. Your PROC FREQ shows the WEEK, PROPENSITY and SALES variables, but then your shot of the DATA appears to show PROPENSITY as a column name or column header???? Are the numbers, such as 2.5, 3.3 and 2.1 shown for LOW the input data values for SALES for the LOW PROPENSITY?? So you only have 1 obs for week 1, 1 obs for week 2 and 1 obs for week 3??? The structure of the input data is not clear to me.

However, PROC FREQ will only show counts (frequencies) and percents (and based on the "grand total" -- since you are suppressing the row percent, the col percent and the overall percent, then I'm not sure what number you are getting in your table -- I assume that you are just showing the SALES in the cells in PROC FREQ, since a frequency of 1 weighted by SALES should only give you the individual sales numbers in each cell, assuming you have a freq count of 1 per week/propensity combination.

If you want averages (the MEAN statistic), then you need to move to a procedure that will calculate the MEAN statistic, such as PROC TABULATE. PROC TABULATE will give you averages for each ROW (WEEK, as shown in your table) or down each COLUMN (for PROPENSITY) as shown in your table. Of course, you can also get the MEAN statistic out of PROC MEANS, but the results are not arranged in cross-tabular form. In addition, PROC REPORT could generate the MEAN statistic, but if you want the mean for both the ROW and the COL, then PROC TABULATE would be the way to go.

There are many previous forum postings on the use of PROC TABULATE and many user-group papers that should come up with a Google search argument, such as:

**SAS tabulate beginner**

cynthia

However, PROC FREQ will only show counts (frequencies) and percents (and based on the "grand total" -- since you are suppressing the row percent, the col percent and the overall percent, then I'm not sure what number you are getting in your table -- I assume that you are just showing the SALES in the cells in PROC FREQ, since a frequency of 1 weighted by SALES should only give you the individual sales numbers in each cell, assuming you have a freq count of 1 per week/propensity combination.

If you want averages (the MEAN statistic), then you need to move to a procedure that will calculate the MEAN statistic, such as PROC TABULATE. PROC TABULATE will give you averages for each ROW (WEEK, as shown in your table) or down each COLUMN (for PROPENSITY) as shown in your table. Of course, you can also get the MEAN statistic out of PROC MEANS, but the results are not arranged in cross-tabular form. In addition, PROC REPORT could generate the MEAN statistic, but if you want the mean for both the ROW and the COL, then PROC TABULATE would be the way to go.

There are many previous forum postings on the use of PROC TABULATE and many user-group papers that should come up with a Google search argument, such as:

cynthia

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-03-2010 11:31 PM

Many thanks Cynthia for pointing me in the right direction. I now understand the limitation of PROC MEANS, and I shall look into PROC TABULATE instead.

(The dataset has three columns: WEEK, PROPENSITY and SALES) Having struggled to get the MEAN statistic (for SALES) arranged in cross-tabular form via PROC MEANS, I tried PROC FREQ but without success;

Owe to urgency, I abandoned SAS and exported the data into excel pivot table with WEEK as row, PROPENSITY as column to obtain the average SALES in the cross-tabular form my boss needed. (Will investigate whether PROC TABULATE can do the same.)

(The dataset has three columns: WEEK, PROPENSITY and SALES) Having struggled to get the MEAN statistic (for SALES) arranged in cross-tabular form via PROC MEANS, I tried PROC FREQ but without success;

Owe to urgency, I abandoned SAS and exported the data into excel pivot table with WEEK as row, PROPENSITY as column to obtain the average SALES in the cross-tabular form my boss needed. (Will investigate whether PROC TABULATE can do the same.)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-04-2010 04:54 AM

Hmmm...

As what you describe , proc report would be a better choice.

Cynthia@sas can do this . hehe.

As what you describe , proc report would be a better choice.

Cynthia@sas can do this . hehe.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-04-2010 06:13 AM

Thanks Ksharp. This works:

PROC REPORT data=work.dataset;

COLUMN week propensity, sales, MEAN;

DEFINE sales / ANALYSIS;

DEFINE week / GROUP;

DEFINE propensity / ACROSS;

run;

PROC REPORT data=work.dataset;

COLUMN week propensity, sales, MEAN;

DEFINE sales / ANALYSIS;

DEFINE week / GROUP;

DEFINE propensity / ACROSS;

run;