Programming the statistical procedures from SAS

Cross Tab Help

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Cross Tab Help

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!


Accepted Solutions
Solution
‎02-02-2017 03:32 PM
Super User
Posts: 18,567

Re: Cross Tab Help

Add in the statement:

 

WEIGHT TOTAL_SPEND;

 

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

View solution in original post


All Replies
Solution
‎02-02-2017 03:32 PM
Super User
Posts: 18,567

Re: Cross Tab Help

Add in the statement:

 

WEIGHT TOTAL_SPEND;

 

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

PROC Star
Posts: 294

Re: Cross Tab Help

Thumbs up to this for ease.

 

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

Super User
Posts: 18,567

Re: Cross Tab Help

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

Occasional Contributor
Posts: 5

Re: Cross Tab Help

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!

Super User
Posts: 18,567

Re: Cross Tab Help

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;

PROC Star
Posts: 294

Re: Cross Tab Help

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 159 views
  • 1 like
  • 3 in conversation