topic Re: Report in SAS Programming

Report

Ronein — Wed, 30 Dec 2020 08:13:02 GMT

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;

Re: Report

Shmuel — Wed, 30 Dec 2020 09:40:21 GMT

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;

Re: Report

Shmuel — Wed, 30 Dec 2020 12:15:43 GMT

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;

Re: Report

andreas_lds — Wed, 30 Dec 2020 12:46:40 GMT

And which colours to use for teams 4 to 42?

Re: Report

Ronein — Wed, 30 Dec 2020 12:49:45 GMT

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;

Re: Report

Shmuel — Wed, 30 Dec 2020 13:26:56 GMT

@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.