Different Summary for the same BREAK Variable

sas_cc — Thu, 28 Jun 2018 20:04:51 GMT

Hi SAS Experts,

The data I have now is:

Name Measurement Jan Feb Mar Apr May

a z # # ...

a y # ...

a x ...

b z

b y

b x

My code is:

PROC REPORT NOWD;

COLUMN name measurement jan feb ...;

DEFINE name ...;

DEFINE measurement ....;

...;

BREAK BEFORE name / SUMMARIZE...;

As a result, I get the SUM for each column based on the name. That is,

Name Measurement Jan Feb Mar Apr May

a Sum Sum ....

a z # # ...

a y # ...

a x ...

b Sum Sum ....

b z # # ...

b y # ...

b x ...

How do I tweak the BREAK statement so I can compute the average for the NAME a, and sum for the NAME b, that is:

Name Measurement Jan Feb Mar Apr May

a Average Average ....

a z # # ...

a y # ...

a x ...

b Sum Sum ....

b z # # ...

b y # ...

b x ...

Thank you very much!

Re: Different Summary for the same BREAK Variable

BrunoMueller — Fri, 29 Jun 2018 15:06:53 GMT

Please explain the use case for this, seems to be a strange report.

You can do this quite well using Proc REPORT and a compute block. You will need to have all the month columns twice, once for the SUM and once for the MEAN.

You can then check in which line of the report you are and fill the cells accordingly.

The code below uses the duplicate month columns with different statistics and a compute block to assign the proper value depending in which line you are the _BREAK_ variable tells you when you are on a break line. The _dummy variable shows the values.

data have;
  length name measure $ 8;
  array months{*} jan feb;
  do name = "a", "b";
    do measure = "x", "y", "z";
      do j = 1 to 5;
      do i = 1 to dim(months);
        months{i} = ceil(ranuni(123) * 100);
      end;
      output;
      end;
    end;
  end;
run;

/*%let doNotPrint = noprint;*/
%let doNotPrint = ;

proc report data=have;
  column name measure jan=jan_sum jan=jan_mean feb=feb_sum feb=feb_mean _dummy;
  define name / group;
  define measure / group;
  define jan_sum / analysis sum "jan" format=comma12.2;
  define jan_mean / analysis mean "jan mean" &doNotPrint;
  define feb_sum / analysis sum "feb" format=comma12.2;
  define feb_mean / analysis mean "feb mean" &doNotPrint;
  define _dummy / computed &doNotPrint;

  break before name / summarize ;

  compute _dummy / char length=32;
    _dummy = catx(":", name, _break_);

    if name = "a" and _break_ = "name" then do;
      jan_sum = jan_mean;
      feb_sum = feb_mean;
    end;
  endcomp;
run;

topic Different Summary for the same BREAK Variable in ODS and Base Reporting

Different Summary for the same BREAK Variable

Re: Different Summary for the same BREAK Variable