The SAS Output Delivery System and reporting techniques

How do I sum select groups in PROC REPORT

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How do I sum select groups in PROC REPORT

Dear all,

 

I have this code:

 

PROC REPORT DATA=WORK.REPORT_DATA ;

COLUMNS Metricrank METRIC_LEVEL_2 Amounts ,SUMMARY_DATE ;

WHERE YEAR ne 2017;

DEFINE METRICRANK / GROUP FORMAT=Metricreorder. order=internal '';

DEFINE METRIC_LEVEL_2 / GROUP '';

DEFINE SUMMARY_DATE / across order=internal 'FY 2016' FORMAT=YYMMD7.;

DEFINE AMOUNTS / ANALYSIS SUM FORMAT=COMMA20.2 '';

Compute

COMPUTE AFTER METRICRANK;

LINE '';

ENDCOMP;

 

RUN;

 

which generates a report as such (without the Totals)

 

Metricrank

METRIC_LEVEL_2

31/01/2016

29/02/2016

31/03/2016

30/04/2016

31/05/2016

30/06/2016

31/07/2016

31/08/2016

30/09/2016

31/10/2016

30/11/2016

31/12/2016

31/01/2017

28/02/2017

31/03/2017

30/04/2017

31/05/2017

30/06/2017

31/07/2017

31/08/2017

1

Avg Debit Balances

1,933.19

1,928.39

1,909.33

1,980.59

1,977.09

1,992.60

1,986.33

1,977.66

1,956.16

2,016.96

2,024.24

2,041.70

2,019.76

1,929.44

1,934.39

1,933.58

1,914.24

1,898.88

1,887.41

1,862.88

 

RWA

1,861.07

1,868.95

1,851.91

1,897.52

1,891.22

1,935.29

1,925.81

1,925.42

1,914.89

1,989.04

1,716.48

1,716.35

1,681.40

1,606.18

1,599.92

1,614.26

1,565.14

1,532.58

1,503.88

 
                      

2

Commission / Fees

0.05

0.04

0.08

0.14

0.06

0.09

0.06

0.07

0.10

0.14

0.06

0.06

0.07

0.10

0.12

0.09

0.11

0.07

0.09

0.09

 

Fund Transfer Pricing

-1.10

-1.03

-1.07

-1.06

-1.09

-1.06

-1.09

-1.09

-1.04

-1.11

-1.08

-1.13

-1.12

-1.10

-1.09

-1.05

-1.09

-1.05

-1.08

-1.08

 

Income

3.37

3.35

2.98

3.95

3.62

3.37

3.80

3.67

3.16

4.64

3.61

3.46

3.40

3.88

3.22

3.32

3.29

-0.04

0.05

-0.88

 

Net Interest Income

0.16

0.15

0.18

0.22

0.24

0.22

0.22

0.21

0.19

0.22

0.21

0.22

0.22

0.19

0.23

0.24

0.24

3.89

4.42

4.72

 

Total:

2.49

2.52

2.16

3.25

2.83

2.61

2.99

2.86

2.40

3.90

2.81

2.61

2.57

3.07

2.47

2.61

2.55

2.87

3.48

2.86

                      

3

Allocated Cost

-1.05

-1.14

-0.95

-1.22

-1.23

-1.31

-1.22

-0.77

-1.54

-0.76

-0.75

-1.57

-1.19

-0.87

-0.99

-1.36

-1.31

-1.09

-0.60

 
 

Client Variable Cost

-0.48

-1.01

-0.73

-0.55

-0.63

-0.52

-0.61

-0.53

-0.54

-0.70

-0.58

-0.61

-0.77

-0.83

-0.70

-0.67

-0.65

-0.75

-0.71

-0.58

 

Portfolio Variable Cost

-0.07

-1.27

-0.69

-0.64

-0.69

-0.77

-0.42

-0.79

-0.25

-1.28

-1.10

-0.99

-0.63

-0.57

-0.88

-0.64

-0.26

-0.36

-1.11

-0.13

 

Total:

-1.60

-3.41

-2.36

-2.41

-2.55

-2.60

-2.25

-2.09

-2.33

-2.74

-2.43

-3.17

-2.59

-2.28

-2.57

-2.68

-2.23

-2.20

-2.42

-0.71

 

As you can see, I would like to summarize only Metricrank 2 and 3 to get a net figure.  Is this possible?  I was trying Compute with if statements but wasn't successful.

 

Your help is much appreciated.

 

Regards,

Aksel


Accepted Solutions
Solution
4 weeks ago
SAS Super FREQ
Posts: 703

Re: How do I sum select groups in PROC REPORT

You can do this with Proc REPORT, see example below. It uses a computed column as the last column. From this compute block you can control all the column values left of this column. Within a compute block, you have the _BREAK_ variable, that tells whether you are in a break line and then set the values accordingly. The _dummy column is printed to understand what values you have available. You can set it to NOPRINT to suppress printing, the columns created by the across, need to be accessed by their column number like _Cn_.

 

SampleCode:

data forReport;
  set sashelp.cars;
  where make in ("Audi", "Volvo", "Volkswagen");
  select (make);
    when ("Audi") mediRank = 1;
    when ("Volkswagen") mediRank = 2;
    when ("Volvo") mediRank = 3;
    otherwise;
  end;

  keep make driveTrain type Horsepower mediRank;
run;

proc report data=forReport;
  column mediRank type driveTrain, horsePower _dummy;
  define mediRank / group;
  define type / group;
  define driveTrain / across;
  define horsePower / analysis;
  define _dummy / computed;
  break after mediRank / summarize;

  compute _dummy / char length=32;
    _dummy = catx(":", mediRank, _break_);
    if lowcase(_break_) = "medirank" and mediRank ne 1 then do;
      type = "Total";
    end;

    if lowcase(_break_) = "medirank" and mediRank = 1 then do;
      call missing(mediRank, type);
      call missing(_c3_, _c4_);
    end;
  endcomp;
run;

View solution in original post


All Replies
Solution
4 weeks ago
SAS Super FREQ
Posts: 703

Re: How do I sum select groups in PROC REPORT

You can do this with Proc REPORT, see example below. It uses a computed column as the last column. From this compute block you can control all the column values left of this column. Within a compute block, you have the _BREAK_ variable, that tells whether you are in a break line and then set the values accordingly. The _dummy column is printed to understand what values you have available. You can set it to NOPRINT to suppress printing, the columns created by the across, need to be accessed by their column number like _Cn_.

 

SampleCode:

data forReport;
  set sashelp.cars;
  where make in ("Audi", "Volvo", "Volkswagen");
  select (make);
    when ("Audi") mediRank = 1;
    when ("Volkswagen") mediRank = 2;
    when ("Volvo") mediRank = 3;
    otherwise;
  end;

  keep make driveTrain type Horsepower mediRank;
run;

proc report data=forReport;
  column mediRank type driveTrain, horsePower _dummy;
  define mediRank / group;
  define type / group;
  define driveTrain / across;
  define horsePower / analysis;
  define _dummy / computed;
  break after mediRank / summarize;

  compute _dummy / char length=32;
    _dummy = catx(":", mediRank, _break_);
    if lowcase(_break_) = "medirank" and mediRank ne 1 then do;
      type = "Total";
    end;

    if lowcase(_break_) = "medirank" and mediRank = 1 then do;
      call missing(mediRank, type);
      call missing(_c3_, _c4_);
    end;
  endcomp;
run;
Occasional Contributor
Posts: 7

Re: How do I sum select groups in PROC REPORT

Thank you very much!!! Worked like a charm.
Occasional Contributor
Posts: 7

Re: How do I sum select groups in PROC REPORT

Just a quick follow up question:

 

Is it possible to do calculations based on rows/columns?  ie. Can I subtract the total from one specific line from another line? Now that I know how to assign dummy values to identify the rows to display data, I was wondering if it was possible to do calculations.

 

I have been trying to find some articles regarding this, but it seems like it would be easier to do it at the data gathering stage rather than reporting.

 

Regards,

Aksel

SAS Super FREQ
Posts: 703

Re: How do I sum select groups in PROC REPORT

You get a lot of sample programs with your SAS installation, one of this sample programs is called rpt61.sas it is located here ...\SASFoundation\9.4\core\sample, it has some very nice xamples for calculated stuff, the programs dates back to 1990 and was made for listing output, but the calculations are very helpful.
Occasional Contributor
Posts: 7

Re: How do I sum select groups in PROC REPORT

Thank you very much for your reply and apologies for my delayed reply.  I am trying to get the business to give me access to the folder.  Mean time, I will search and see if I can download it from another place.

 

Thanks again for your help.

 

Aksel

SAS Super FREQ
Posts: 703

Re: How do I sum select groups in PROC REPORT

Find attached the code of the rpt61.sas

Attachment
Highlighted
Occasional Contributor
Posts: 7

Re: How do I sum select groups in PROC REPORT

Oh wow! Thank you! You have gone out of your way to sort my troubles. Thank you very much for going the extra distance..
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 384 views
  • 0 likes
  • 2 in conversation