Cross Tab Help

[ciccotelli412]

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!

[Reeza · Accepted solution]

Add in the statement:

WEIGHT TOTAL_SPEND;

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

[collinelliot]

Thumbs up to this for ease.

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

[Reeza]

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

[ciccotelli412]

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!

[Reeza]

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;

[collinelliot]

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.