Proc REPORT Compute Block - Attempt to fill in cells below Group values does not work

[RichardAD]

In the following example I would like the merged empty cells in the Proc REPORT output to be filled in with the current group value.

In ODS LISTING the cells of a repeated group value appear to be always spanned (SPANROWS )

In HTML, SPANROWS has to be turned as a Proc REPORT option.

data have ;
input ID $ Level $ Count Duration Duration_YTD ;
Duration_YTD2 = Duration_YTD ;
datalines ;
Owl YTD 1 20  20
Emu YTD 11  200 200
Jay YTD 12  100 100
Hen YTD 13  300 300
Spy YTD 6 80  80
Auk YTD 7 500 500
Tui YTD 8 60  60
Kea YTD 4 140 140
Fin YTD 6 80  80
Mew YTD 12  70  70
Owl DayX  0 0 20
Emu DayX  3 3 200
Jay DayX  2 4 100
Hen DayX  1 8 300
Spy DayX  1 6 80
Auk DayX  2 7 500
Tui DayX  4 9 60
Kea DayX  1 0 140
Fin DayX  1 0 80
Mew DayX  2 2 70
;
data cntlin ;
infile datalines dlm=',' ;
length FmtName $32 Start End 8 Label $10 ;
input FmtName Start End Label ;
datalines ;  
DurationSize,1,59,S 
DurationSize,60,179,M 
DurationSize,180,5000,L 
DurationRange,1,59,< 60  
DurationRange,60,179,< 180 
DurationRange,180,5000,>= 180 
;

proc format cntlin=cntlin ;
run ;

proc print data=have ;
  format Duration_YTD DurationSize. ;
  format Duration_YTD2 DurationRange. ;
run ;

options linesize=100 nocenter nodate nonumber formdlim=' ' ;

proc tabulate data=have ;
  class Duration_YTD Duration_YTD2 Level ;
  var Count ;
  format Duration_YTD DurationSize. ;
  format Duration_YTD2 DurationRange. ;
  table 
    Duration_YTD
    * Duration_YTD2
      * Level
  ,
    Count * (Sum * f=5. N * f=4.)
  / rts=40
  ;
run;

proc report box data=have ;
  title "Columns under YTD are group" ;
  title2 "Cells subsequent to new group value are blank" ;
  column ('YTD' Duration_YTD Duration_YTD2) Level Count Count=Count_repeat ;
  define Duration_YTD  / group order=internal width=6 format=DurationSize.  'size'  ;
  define Duration_YTD2 / group order=internal width=6 format=DurationRange. 'range' ;
  define Level             / group ;
  define Count             / Analysis Sum ;
  define Count_repeat      / Analysis N ;
run;

proc report box data=have ;
  title "Columns under YTD are group" ;
  title2 "Cells subsequent to new group value are blank, despite COMPUTE block" ;
  column ('YTD' Duration_YTD Duration_YTD2) Level Count Count=Count_repeat ;
  define Duration_YTD  / group order=internal width=6 format=DurationSize.  'size'  ;
  define Duration_YTD2 / group order=internal width=6 format=DurationRange. 'range' ;
  define Level             / group ;
  define Count             / Analysis Sum ;
  define Count_repeat      / Analysis N ;
  compute Duration_YTD ;
    if not missing(Duration_YTD) then hold = Duration_YTD ;
    if     missing(Duration_YTD) then Duration_YTD = hold ;
  endcomp ;
run;

Tabulate

---------------------------------------------------
|                                      |  Count   |
|                                      |----------|
|                                      | Sum | N  |
|--------------------------------------+-----+----|
|Duration_YTD|Duration_YT-|Level       |     |    |
|------------|D2          |            |     |    |
|S           |------------+------------|     |    |
|            |< 60        |DayX        |    0|   1|
|            |            |------------+-----+----|
|            |            |YTD         |    1|   1|
|------------+------------+------------+-----+----|
|M           |< 180       |DayX        |   11|   6|
|            |            |------------+-----+----|
|            |            |YTD         |   48|   6|
|------------+------------+------------+-----+----|
|L           |>= 180      |DayX        |    6|   3|
|            |            |------------+-----+----|
|            |            |YTD         |   31|   3|
---------------------------------------------------
 
                                                                                                    
 
REPORT: Columns under YTD are group
Cells subsequent to new group value are blank

  ------------------------------------------------
  |     YTD                                      |
  |  size   range  Level         Count      Count|
  |----------------------------------------------|  
  |     S| < 60  | DayX    |         0|         1|  
  |      |       |---------+----------+----------|  
  |      |       | YTD     |         1|         1|  
  |------+-------+---------+----------+----------|  
  |     M| < 180 | DayX    |        11|         6|  
  |      |       |---------+----------+----------|  
  |      |       | YTD     |        48|         6|  
  |------+-------+---------+----------+----------|  
  |     L| >= 180| DayX    |         6|         3|  
  |      |       |---------+----------+----------|  
  |      |       | YTD     |        31|         3|  
  ------------------------------------------------  
 
                                                                                                    
 
REPORT: Columns under YTD are group
Cells subsequent to new group value are blank, despite COMPUTE block

  ------------------------------------------------
  |     YTD                                      |
  |  size   range  Level         Count      Count|
  |----------------------------------------------|  
  |     S| < 60  | DayX    |         0|         1|  
  |      |       |---------+----------+----------|  
  |      |       | YTD     |         1|         1|  
  |------+-------+---------+----------+----------|  
  |     M| < 180 | DayX    |        11|         6|  
  |      |       |---------+----------+----------|  
  |      |       | YTD     |        48|         6|  
  |------+-------+---------+----------+----------|  
  |     L| >= 180| DayX    |         6|         3|  
  |      |       |---------+----------+----------|  
  |      |       | YTD     |        31|         3|  
  ------------------------------------------------  

The HTML is showing blank cells when group value would have repeated.

My attempt to force the repeated using a COMPUTE block did not work.

Is there a technique or option to force the group value to repeat?

[Ksharp · Accepted solution]

Post a desired output would be better to explain your question.

You want this ?

data have ;
input ID $ Level $ Count Duration Duration_YTD ;
Duration_YTD2 = Duration_YTD ;
datalines ;
Owl YTD 1 20  20
Emu YTD 11  200 200
Jay YTD 12  100 100
Hen YTD 13  300 300
Spy YTD 6 80  80
Auk YTD 7 500 500
Tui YTD 8 60  60
Kea YTD 4 140 140
Fin YTD 6 80  80
Mew YTD 12  70  70
Owl DayX  0 0 20
Emu DayX  3 3 200
Jay DayX  2 4 100
Hen DayX  1 8 300
Spy DayX  1 6 80
Auk DayX  2 7 500
Tui DayX  4 9 60
Kea DayX  1 0 140
Fin DayX  1 0 80
Mew DayX  2 2 70
;
data cntlin ;
infile datalines dlm=',' ;
length FmtName $32 Start End 8 Label $10 ;
input FmtName Start End Label ;
datalines ;  
DurationSize,1,59,S 
DurationSize,60,179,M 
DurationSize,180,5000,L 
DurationRange,1,59,< 60  
DurationRange,60,179,< 180 
DurationRange,180,5000,>= 180 
;

proc format cntlin=cntlin ;
run ;


proc report box data=have  nowd out=x; 
  title "Columns under YTD are group" ;
  title2 "Cells subsequent to new group value are blank, despite COMPUTE block" ;
  column ('YTD' Duration_YTD  Duration_YTD2 YTD YTD2) Level Count Count=Count_repeat ;
  define Duration_YTD  /noprint group order=internal width=6 format=DurationSize.  'size'  ;
  define Duration_YTD2 /noprint group order=internal width=6 format=DurationRange. 'range' ;
  define YTD  /computed width=6 format=DurationSize.  'size'  ;
  define YTD2 /computed width=6 format=DurationRange. 'range' ;

  define Level             / group ;
  define Count             / Analysis Sum ;
  define Count_repeat      / Analysis N ;

  compute YTD2;
    if not missing(Duration_YTD) then _YTD=Duration_YTD;
	YTD=_YTD;
    if not missing(Duration_YTD2) then _YTD2=Duration_YTD2;
    YTD2=_YTD2;
  endcomp;
run;