Different Summary for the same BREAK Variable

[sas_cc]

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!

[BrunoMueller]

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;