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

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

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

7 REPLIES 7
BrunoMueller
SAS Super FREQ

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;
asdf12_12
Fluorite | Level 6
Thank you very much!!! Worked like a charm.
asdf12_12
Fluorite | Level 6

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

BrunoMueller
SAS Super FREQ
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.
asdf12_12
Fluorite | Level 6

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

BrunoMueller
SAS Super FREQ

Find attached the code of the rpt61.sas

asdf12_12
Fluorite | Level 6
Oh wow! Thank you! You have gone out of your way to sort my troubles. Thank you very much for going the extra distance..

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
  • 7 replies
  • 1305 views
  • 0 likes
  • 2 in conversation