BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ciccotelli412
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

Add in the statement:

 

WEIGHT TOTAL_SPEND;

 

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

collinelliot
Barite | Level 11

Thumbs up to this for ease.

 

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

Reeza
Super User

@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
Calcite | Level 5

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
Super User

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
Barite | Level 11

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

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