topic Cross Tab Help in Statistical Procedures

Cross Tab Help

ciccotelli412 — Thu, 02 Feb 2017 20:07:02 GMT

Hello Everyone,

I just created an account and this is my first time posting on here, really hoping you guys can help!

So currently, I have a dataset with about 4 million rows.

The columns I have are:

Hshld_no, Total_spend, Avg_Gallons, and Loyalty_segment

Here is my question.

When I do a cross tab between Loyalty_segment and Avg_gallons I get an output that Counts the amount of rows that fit those criteria

ods html;
proc freq data = out.Final_merge;
table avg_gallons * Loyalty_seg;
run;

i.e.

Loyalty_Seg

AVG. Gallons 1 2 3 4 5

1 xxx xxx xxx xxx xxx

2 xxx xxx xxx xxx xxx

3 xxx xxx xxx xxx xxx

4 xxx xxx xxx xxx xxx

5 xxx xxx xxx xxx xxx

I was wondering, is there a way to get this to return the sum of the spend of those attributes, rather than the count of the rows.

Loyalty_Seg

AVG. Gallons 1 2 3 4 5

1 500 xxx xxx xxx xxx

2 xxx xxx xxx xxx xxx

3 xxx xxx xxx xxx xxx

4 xxx xxx xxx xxx xxx

5 xxx xxx xxx xxx xxx

So for example, currently it tells me that there are 500 Housholds (rows) that have an average of 1 gallons and are in loyalty_seg 1

How can I see the sum of those 500 households in this same format?

Loyalty_Seg

AVG. Gallons 1 2 3 4 5

1 ??? (SPEND) xxx xxx xxx xxx

2 xxx xxx xxx xxx xxx

3 xxx xxx xxx xxx xxx

4 xxx xxx xxx xxx xxx

5 xxx xxx xxx xxx xxx

I hope this makes sense!

Re: Cross Tab Help

Reeza — Thu, 02 Feb 2017 20:20:06 GMT

Add in the statement:

WEIGHT TOTAL_SPEND;

You can also use a different proc, such as PROC TABULATE to explicitly control the output.

Re: Cross Tab Help

collinelliot — Thu, 02 Feb 2017 20:24:22 GMT

proc report or proc tabulate are what you want.

For example:

proc tabulate data = your_data format = dollar12.;

class avg_gallons Loyalty_seg;

var Total_spend;

table avg_gallons,

loyalty_seg*total_spend*sum;

run;

You can modify as necessary to get the exact output you want.

Re: Cross Tab Help

collinelliot — Thu, 02 Feb 2017 20:28:25 GMT

Thumbs up to this for ease.

I think with proc tabulate you can totals, subtotals, and format more easily, if those things matter.

Re: Cross Tab Help

ciccotelli412 — Thu, 02 Feb 2017 20:34:10 GMT

Super easy! Thank you

Another quick question, I notice that it is putting my output in scientific notation, when I copy and paste into excel, it is rounding to 6 digits, is there a way to control that?

Thank you again!

Re: Cross Tab Help

Reeza — Thu, 02 Feb 2017 20:35:28 GMT

@collinelliot Totally agree, as soon as you want to customize any of the proc freq output it's time to switch to proc tabulate.

You get control over the totals, labels, formats and order much more easily in proc tabulate.

Re: Cross Tab Help

Reeza — Thu, 02 Feb 2017 20:54:36 GMT

You don't have control when you copy and paste.

You can use ODS EXCEL to directly write the output to an Excel file.

But to customize the format of the values you need to modify the template (HARD) or switch to proc tabulate (EASY) and @collinelliot has already provided the proc tabulate code with formats.

Then sandwich it between the ODS EXCEL statements to generate your output.

ods excel file='path to excel file';

proc tabulate...

ods excel close;