PROC Report Summary Column for Rows with different metrics

[asdf12_12]

Dear all,

I have managed to create a report (thanks to all the help from Mr.Bruno_SAS) exactly the way I wanted by using:

PROC REPORT DATA=WORK.RANKED_New 
STYLE(HEADER) = {font_size=6pt}
STYLE(COLUMN) = {font_size=6pt}
STYLE(REPORT) = {font_size=6pt cellpadding=2px}

;
COLUMNS Ranking Grouping Ranking_sub METRIC_LEVEL_2 Amounts ,SUMMARY_DATE _dummy;
WHERE YEAR ne 2016;
BY BI_Marker;
DEFINE Ranking / GROUP  '' Left noprint;
DEFINE Grouping / Group order =internal '' Left;
DEFINE Ranking_sub / GROUP  '' Left noprint;
DEFINE METRIC_LEVEL_2 / GROUP '';
DEFINE SUMMARY_DATE / across order=internal 'FY 2017' ; /*ddmmyyd10.*/

DEFINE AMOUNTS / ANALYSIS SUM FORMAT=COMMA20.2 '';
DEFINE _dummy / computed noprint;
break after ranking / summarize;

COMPUTE _dummy / CHAR LENGTH = 21;
	_dummy = catx(":",ranking, _break_);
    IF lowcase(_break_) = "ranking" AND ranking not in ('a','b','e','f','i') THEN DO;
      METRIC_LEVEL_2 = "Total";
	  call missing(ranking);
	
    END;
    IF lowcase(_break_) = "ranking" AND ranking in ('a','b','e','f','i') THEN DO;
      call missing(ranking, METRIC_LEVEL_2);
	  call missing(_c3_, _c4_,_c5_, _c6_,_c7_, _c8_,_c9_, _c10_,_c11_, _c12_,_c13_, _c14_,_c15_,_c16_);
    END;

ENDCOMP;


COMPUTE AFTER Ranking;
	LINE '';
ENDCOMP;
RUN;

This creates this output: (not the best image, had to be redacted)

I would like to create a column at the end of 31DEC2016 called FY2016 and for each row apply a different calculation.

ie. for the Avg Asset Balances row, it should be straight average for the corresponding row under FY2016

for Comission/Fess, Income, Cost metrics should be aggregations (sum of the whole year)

I was thinking perhaps a dummy column with an if statement? something like,

compute dummy2;

          if Metric_Level_2 = 'Avg Asset Balances' then average(_c3_,_c4_,_c5_ etc) end;

          if METRIC_LEVEL_2 = 'Commission/Fees' then sum(_c3_,_c4_,_c5_ etc) end;

endcomp;

I know that I can refer to the columns in calculations, but I was wondering can we refer to rows as well in SAS? _r1_, _r2_ etc. as I would also like to calculate differences within the newly created FY2016 column.

I know a lot to ask, but any direction, sample, report, paper would be more than appreciated.

Thanks,

Aksel

[Cynthia_sas · Accepted solution]

Hi:

  To get you started, you can use a simple alias technique for your right-most column to get a summary column or an average column, as shown in this example:

  In this example the "Overall Total" and "Overall Average" columns are based on the SALES variable, which is nested underneath the REGION variable. The column LASTCOL is computed as shown above. On one row, in the last column, I can refer to any other variable value on the line. In my example, I did not use absolute column numbers to make the COMPUTE block simpler.

  Here's the entire program:

title; footnote;
proc report data=sashelp.shoes;
  where region in ('Asia' 'Canada') and
        product in ('Boot' 'Sandal' 'Slipper');
  column product region,sales ('Overall' sales=totsales) ('Overall' sales=avgsales) ('Computed' lastcol);
  define product /group;
  define region / across;
  define sales / sum;
  define totsales / sum 'Total';
  define avgsales / mean 'Average';
  define lastcol / computed;
  compute lastcol;
    if product = 'Boot' then lastcol=totsales/100;
	else if product = 'Sandal' then lastcol = avgsales*10;
	else if product = 'Slipper' then lastcol = 999999;
  endcomp;
run;

Note how SALES was used 3 times on the COLUMN statement:

1) nested underneath region

2)  used a second time with an alias of TOTSALES and a DEFINE statement specifying the SUM statistic

3) used a third time with an alias of AVGSALES and a DEFINE statement specifying the MEAN statistic

  Then my LASTCOL item was defined as a computed column and had a COMPUTE block for calculations. By default, you cannot refer to previous or upcoming rows. PROC REPORT writes one report row at a time on the report and does not have visibility of what it previously wrote or what it is about to write. PROC REPORT has visibility of only one row at a time. You can create temporary variables if you need to "save" numbers from previous rows if you need them, but I rarely do this for more than one or two values because it gets cumbersome to manage.

  There's an example at the very end of this paper http://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf that illustrates this technique for "holding" a value for a subgroup in order to use that value in a division. Look on pages 24-26 -- the topic starts with a discussion of using PCTSUM.

cynthia

[asdf12_12]

Thank you very much!

I was worried that the first part of my ask was a bit confusing, but this will work great.  

Regards.

Aksel