BookmarkSubscribeRSS Feed
Laurie
Calcite | Level 5

Hi,

I have a problem creating subtotals.  I am outputting using proc report to excel.   So I want the totals in the appropriate column (don't want to use the line command).  I have the following code and output.  I also listed at the very bottom what I want as the ultimate output.  I am at a loss how to use another set of "fake" breaks to create a subtotal by market.  Can anyone help?

Data;

t b Greater Philadelphia Touchpoint 2

t b New England South Touchpoint 7

t b New York Branch Intranet 0

t b New York Touchpoint 0

t b Ohio Touchpoint 0

t b Western/Central Pennsylvania Touchpoint 1

t b _Incomplete Information Branch Intranet 1

t b _Incomplete Information Touchpoint 2

Code:

ods listing close;

ODS tagsets.excelxp

file = "\\wapprib00001040\Prod\output\excel\TESTRPT_sub.xls"

  style = Barrettsblue

  options ( 

           sheet_label = ''

               wraptext='yes'

           /*absolute_column_width='20'*/);

/**************************************************************

Output Market Trend

****************************************************************/

ods tagsets.excelxp   options(sheet_name='Market Trend'   );

proc report data = market_trend_tmp1  headskip split = '*' missing wrap ;

column brkt brkb  market_name channel_summary

        total_accts_m1 total_accts_m1=SumAccounts1 ;

define market_name / group 'Market Name' ;

define channel_summary / group 'Channel';

define brkt / group  ;

define brkb / group ;

define total_accts_m1 / sum  'Accounts'  ;

define SumAccounts1 / sum  ;

  

/*break after market_name / summarize;*/

break after brkt / summarize;

break after brkb / summarize;

compute SumAccounts1;

if channel_summary = 'Touchpoint' then TPTSumAccounts1 + SumAccounts1;

else if channel_summary = 'Branch Intranet' then BRISumAccounts1 + SumAccounts1;

endcomp;

compute after brkt;

  total_accts_m1.sum = TPTSumAccounts1;

  channel_summary = 'Number of Touchpoint';

endcomp;

  compute after brkb;

  total_accts_m1.sum = BRISumAccounts1;

  channel_summary = 'Number of Branch Intranet';

endcomp;

run ;

ods tagsets.excelxp close;

ods listing;

Output Have from above:

brktbrkbMarket NameChannelAccountstotal_accts_m1
tbGreater PhiladelphiaTouchpoint22
New England SouthTouchpoint77
New YorkBranch Intranet00
Touchpoint00
OhioTouchpoint00
Western/Central PennsylvaniaTouchpoint11
_Incomplete InformationBranch Intranet11
Touchpoint22
tb Number of Branc113
t Number of Touch1213


I want the below output:

brktbrkbMarket NameChannelAccountstotal_accts_m1
tbGreater PhiladelphiaTouchpoint22
Need subtotals by Market (broken out) --------->Subtotal of Branc0
Need subtotals by Market (broken out) --------->Subtotal of Touch2
New England SouthTouchpoint77
Need subtotals by Market (broken out) --------->Subtotal of Branc0
Need subtotals by Market (broken out) --------->Subtotal of Touch7
New YorkBranch Intranet00
Touchpoint00
Need subtotals by Market (broken out) --------->Subtotal of Branc0
Need subtotals by Market (broken out) --------->Subtotal of Touch0
OhioTouchpoint00
Need subtotals by Market (broken out) --------->Subtotal of Branc0
Need subtotals by Market (broken out) --------->Subtotal of Touch0
Western/Central PennsylvaniaTouchpoint11
Need subtotals by Market (broken out) --------->Subtotal of Branc0
Need subtotals by Market (broken out) --------->Subtotal of Touch1
_Incomplete InformationBranch Intranet11
Touchpoint22
Need subtotals by Market (broken out) --------->Subtotal of Branc0
Need subtotals by Market (broken out) --------->Subtotal of Touch1
tbI have this workingGrand Total of Branc113
t I have this workingGrand Total of Touch1213
5 REPLIES 5
Laurie
Calcite | Level 5

Yes thanks for your reply!   I have too many additional statistics to do a union.  I did get it to work as stated above for the grand totals without doing a union.  But now need subtotals as well.

This is only a dumbed down example of the actual report code.  I have 12 months of data and each month has 5 statistics to sum up.  Also I am using macros to output it since the number of months to display is dynamic (rolling 12 months and accumulating months from report inception i.e. today I might have 1 month of data to display and 3 months from now I have 3 months to display).   It would be easier in a proc report to do the calculations if possible.

Maybe there is a way to add more dummy break lines to get the subtotals by market to work?


RW9
Diamond | Level 26 RW9
Diamond | Level 26

You do not need to union, do your sums with a by group:

proc sql;

     insert into DATA

     select     MONTH,

                   SUM(STAT1) as STAT1,

                   SUM(STAT2) as STAT2

                    ...

     group by MONTH;

run;

There are many ways to get totals/statistics and put them into datasets.  With the by grouping you also don't need to know the actual groups up front.

Laurie
Calcite | Level 5

Thanks again for your answer.  I have way too many variables to do the calcs in sql.  I am still searching for another way to get subtotals.  I have the grand totals working so I was hoping for some help on the subtotals.  If there is a way in proc report I would welcome any thoughts.  Thanks again I appreciate the reply.

Thanks!

Cynthia_sas
SAS Super FREQ

Hi:

  You can make extra break lines with PROC REPORT. But you will need a "helper" variable for every break line. This paper http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf has an example starting on page 7. I have some sample code that uses SASHELP.CLASS, so it should be easy for you to run and see the difference between program #1, #2 and #3 (where all the "helper" variables are hidden. It should be easy to "macro-ize" code like this because what you add in your "extra" compute block for your helper variables should fall into some pattern that lends itself to generation with a macro program.

 

Cynthia

** make some fake data with SASHELP.CLASS;

** all helper variables are called "xline";

data helper;

  length sex name $25;

  set sashelp.class;

  xline1 = 'F_mean';

  xline2 = 'M_mean';

  xline3 = 'height';

  xline4 = 'weight';

   

  ** make a few "sum" variables which will be 0 or 1 based on condition;

  if height > 66 then tall=1;

  else tall = 0;

  if weight > 125 then heavy = 1;

  else heavy = 0;

run;

ods listing close;

    

options missing = ' ';

ods html file='c:\temp\use_helper_var.html';

proc report data=helper nowd;

title '1) show all helper variables';

title2 'notice how all extra summary lines show same statistics';

  column xline1 xline2 xline3 xline4 tall heavy sex name age height weight;

  define xline1 / order;

  define xline2 / order;

  define xline3 / order;

  define xline4 / order;

  define tall / sum;

  define heavy / sum;

  define sex / order;

  define name / order;

  define age / display;

  define height / mean f=8.2;

  define weight / mean f=8.2;

  break after xline1 / summarize;

  break after xline2 / summarize;

  break after xline3 / summarize;

  break after xline4 / summarize;

  rbreak after / summarize;

run;

          

proc report data=helper nowd;

title '2) reassign totals from helper variables tall and heavy';

title2 'and grab female and male averages to use on extra break lines';

  column xline1 xline2 xline3 xline4 tall heavy sex name age height weight;

  define xline1 / order;

  define xline2 / order;

  define xline3 / order;

  define xline4 / order;

  define tall / sum;

  define heavy / sum;

  define sex / order;

  define name / order;

  define age / display;

  define height / mean f=8.2;

  define weight / mean f=8.2;

  compute before sex;

    ** grab and hold stats for each gender in temp variables;

    if sex = 'F' then do;

       ht_F_mean = round(height.mean,.01);

       wt_F_mean = round(weight.mean,.01);

    end;

else if sex = 'M' then do;

       ht_M_mean = round(height.mean,.01);

       wt_M_mean = round(weight.mean,.01);

    end;

  endcomp;

  break after xline1 / summarize;

  break after xline2 / summarize;

  break after xline3 / summarize;

  break after xline4 / summarize;

  rbreak after / summarize;

  compute after xline1;

    sex = 'Female Averages';

height.mean = ht_F_mean;

weight.mean = wt_F_mean;

  endcomp;

  compute after xline2;

    sex = 'Male Averages';

height.mean = ht_M_mean;

weight.mean = wt_M_mean;

  endcomp;

  compute after xline3;

     sex = 'Students over 66" tall';

  height.mean = tall.sum;

  call define('height.mean','format','8.0');

  weight.mean = .;

  endcomp;

  compute after xline4;

     sex = 'Students over 125 lbs';

  weight.mean = heavy.sum;

  call define('weight.mean','format','8.0');

  height.mean = .;

  endcomp;

  compute after;

     sex = 'Overall Averages';

  endcomp;

run;

       

proc report data=helper nowd;

title '3) Using NOPRINT with same code';

  column xline1 xline2 xline3 xline4 tall heavy sex name age height weight;

  define xline1 / order noprint;

  define xline2 / order noprint;

  define xline3 / order noprint;

  define xline4 / order noprint;

  define tall / sum noprint;

  define heavy / sum noprint;

  define sex / order;

  define name / order;

  define age / display;

  define height / mean f=8.2;

  define weight / mean f=8.2;

  compute before sex;

    ** grab and hold stats for each gender in temp variables;

    if sex = 'F' then do;

       ht_F_mean = round(height.mean,.01);

       wt_F_mean = round(weight.mean,.01);

    end;

else if sex = 'M' then do;

       ht_M_mean = round(height.mean,.01);

       wt_M_mean = round(weight.mean,.01);

    end;

  endcomp;

  break after xline1 / summarize;

  break after xline2 / summarize;

  break after xline3 / summarize;

  break after xline4 / summarize;

  rbreak after / summarize;

  compute after xline1;

    sex = 'Female Averages';

height.mean = ht_F_mean;

weight.mean = wt_F_mean;

  endcomp;

  compute after xline2;

    sex = 'Male Averages';

height.mean = ht_M_mean;

weight.mean = wt_M_mean;

  endcomp;

  compute after xline3;

     sex = 'Students over 66" tall';

  height.mean = tall.sum;

  call define('height.mean','format','8.0');

  weight.mean = .;

  endcomp;

  compute after xline4;

     sex = 'Students over 125 lbs';

  weight.mean = heavy.sum;

  call define('weight.mean','format','8.0');

  height.mean = .;

  endcomp;

  compute after;

     sex = 'Overall Averages';

  endcomp;

run;

ods html close;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1508 views
  • 0 likes
  • 3 in conversation