Cross-tabulate the average of a third variable

[deleted_user]

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

[Cynthia_sas]

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

[deleted_user]

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.)

[Ksharp]

Hmmm...
As what you describe , proc report would be a better choice.
Cynthia@sas can do this . hehe.

[deleted_user]

Thanks Ksharp. This works:

PROC REPORT data=work.dataset;
COLUMN week propensity, sales, MEAN;
DEFINE sales / ANALYSIS;
DEFINE week / GROUP;
DEFINE propensity / ACROSS;
run;