Report

[Ronein]

Hello

I want to create summary report as following:

column 1: Year

column 2: status

column 3 :Number of rows in team=1

column 4 :Number of rows in team=1 with ind99=1

column 5 : PCT of  column4/column3

column 6 :Number of rows in team=2
column 7 :Number of rows in team=2 with ind99=1
column 8 : PCT of  column7/column6

column 9 :Number of rows in team=3
column 10 :Number of rows in team=3 with ind99=1
column 11 : PCT of  column10/column9

I also want to have total row.

I want that columns headers be in light grey background.

I want that columns  3,6,9 will be in yellow background

I want that columns  4,7,10 will be in blue  background

I want that columns  5,8,11 will be in green  background

I want that the report be dynamic so if there are more teams(More then 3 teams) then the report will still be created well

Data rawtbl;
Input year ID team  status $ Ind99;
cards;
2020 1 1 Y 0
2020 2 1 Y 1
2020 3 1 N 0
2020 4 3 N 0
2020 5 2 Y 0
2020 6 2 Y 1
2020 7 2 Y 1
2020 8 2 Y 0
2020 9 3 Y 0
2020 10 3 N 0
2019 1 2 Y 1
2019 2 1 Y 0
2019 3 1 Y 0
2019 4 1 N 1
2019 5 1 N 1
2019 6 2 Y 1
2019 7 2 Y 0
2019 8 3 Y 0
2019 9 3 N 0
2019 10 3 N 1
;
Run;

[Shmuel]

I'm using SAS UE and cannot test colors.

Try next code:

proc summary data=rawtbl nway noprint;
  class year status team;
  var ind99;
  output out=tbl(drop=_type_ rename=(_freq_=rows)) sum=;
run;

data want;
 set tbl;
     pct = ind99/rows;
     format pct percent6.2;
run;

proc report data=want;
  columns year status team rows ind99 pct;
  define year / order;
  define status / order;
  define team / order;
  define rows / sum f=comma4. color=yellow;
  define ind99 / sum f=comma4. color=blue;
  define pct / display f=percent6. color=green;
run;

[Ronein]

Why are you using  "sum" in proc report  when you just need to display the table ?

for pct you use display and for rows and ind99 you use sum,

define rows / sum f=comma4. color=yellow;
define ind99 / sum f=comma4. color=blue;
define pct / display f=percent6. color=green;

[Shmuel]

---

@Ronein wrote:

Why are you using  "sum" in proc report  when you just need to display the table ?

for pct you use display and for rows and ind99 you use sum,

define rows / sum f=comma4. color=yellow;
define ind99 / sum f=comma4. color=blue;
define pct / display f=percent6. color=green;

---

Rows contains frequency. Sum is used for the Total row.

Ind99 contains 1 or 0; Thus sum result in total number of rows with ind99=1;

PCT is already computed per group and need be computed for the Total row.

It cannot be summed.

You may try change SUM to DISPLAY and check how should proc report relate to it.

[Shmuel]

Alternative code creates a color report:

proc summary data=rawtbl nway noprint;
  class year status team;
  var ind99;
  output out=tbl(drop=_type_ rename=(_freq_=rows)) sum=;
run;

proc report data=tbl;
  columns year status team rows ind99 pct;
  define year / order;
  define status / order;
  define team / order;
  define rows / sum f=comma4. style={backgroundcolor=yellow};
  define ind99 / sum f=comma4. style={backgroundcolor=cyan};
  define pct / computed f=percent6. "PCT" style={backgroundcolor=lightgreen};
  
compute pct;
  pct=sum(ind99.sum / rows.sum);
endcomp;

compute after;
   status='Total';  /* must be a char type column variable */
endcomp;

rbreak after /summarize;  /* report summary */
run;

[andreas_lds]

And which colours to use for teams 4 to 42?