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

Hi all,

I am doing a simple Sum using the Group By statement and I got what I wanted -- the total members in each state and total spend in each state.

Now, I was thinking if it's possible to add another row that shows the overall national total (sum of all states).

Below is my simplified code.

proc sql;

    create table SUM_BY_STATE as

    SELECT

           STATE

           ,sum(MEMBER_COUNT) as TOT_MBR_COUNT       

           ,sum(MEMBER_SPEND) as TOT_MBR_SPEND

    FROM RECORD1

    GROUP BY STATE

    order by STATE;

quit;

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

you could always just insert the total..

proc sql;

    create table SUM_BY_STATE as

    SELECT

           STATE

           ,sum(MEMBER_COUNT) as TOT_MBR_COUNT       

           ,sum(MEMBER_SPEND) as TOT_MBR_SPEND

    FROM RECORD1

    GROUP BY STATE

    order by STATE;

insert into SUM_BY_STATE (state, mbr_count, mbr_spend)

select

     'Grand Total' as State,

     sum(mbr_count),

     sum(mbr_spend)

from record1;

quit;

View solution in original post

2 REPLIES 2
DBailey
Lapis Lazuli | Level 10

you could always just insert the total..

proc sql;

    create table SUM_BY_STATE as

    SELECT

           STATE

           ,sum(MEMBER_COUNT) as TOT_MBR_COUNT       

           ,sum(MEMBER_SPEND) as TOT_MBR_SPEND

    FROM RECORD1

    GROUP BY STATE

    order by STATE;

insert into SUM_BY_STATE (state, mbr_count, mbr_spend)

select

     'Grand Total' as State,

     sum(mbr_count),

     sum(mbr_spend)

from record1;

quit;

skillman
SAS Employee

You can also union the total at the bottom of the SQL:

proc sql;

    create table SUM_BY_STATE as

    SELECT

           STATE

           ,sum(MEMBER_COUNT) as TOT_MBR_COUNT      

           ,sum(MEMBER_SPEND) as TOT_MBR_SPEND

    FROM RECORD1

    GROUP BY STATE

union

    SELECT

           'Grand Total' as State,

           ,sum(MEMBER_COUNT) as TOT_MBR_COUNT      

           ,sum(MEMBER_SPEND) as TOT_MBR_SPEND

    FROM RECORD1

quit;

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 Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 54505 views
  • 5 likes
  • 3 in conversation