Is it possible to adjust the summary line value in a Proc REPORT across column ?

[RichardAD]

I have some count of items data and I also want the summary line to show the count of distinct items.

Two issues I have are:

• I can't change the _col_.sum value
  • Tried _n_.sum = # and colname.sum = # got errors such as
  • ERROR: The variable type of 'ROW_COUNT.SUM'n is invalid in this context.
  • ERROR: The variable type of '_COL_.SUM'n is invalid in this context.
• I can't figure out how to get pretext= to render inline styling ^{newline} 
  • For html destination <BR> in pretext would render a newline (but I want to also have Excel output).

Sample output so far

Sample code

data raw ;
  call streaminit(2024) ;
  do cat = 'M', 'A', 'Q', 'X', 'Z' ;
    do subcat = 'T', 'U', 'V', 'W' ;
      do _n_ = 1 to rand('integer', 2000) ;
        key = rand('integer', 3000);
        output ;
      end ;
    end ;
  end ;
run ;

proc sql ; 
  create table counts as
  select cat length=20, subcat, count(*) as row_count
  from raw
  group cat, subcat
  ;
quit ;

proc sql noprint ;
  create table ukey_counts as
  select subcat
  , case subcat 
      when 'T' then 'a,b'
      when 'U' then 'd,e,f'
      when 'V' then 'id'
      when 'W' then 'g,h' 
      else ''
    end as keyitems
  , count(distinct key) as subcat_ukey_count
  from raw
  group subcat
  ;
quit ;

data _null_;
  set ukey_counts ;
  call symputx(cats('_items',_n_), keyitems);
  call symputx(cats('_',_n_), cats(subcat_ukey_count));
run;

ods html file = 'report.html' style=plateau;
ods escapechar = '^' ;

proc print data=sashelp.vmacro ;
  where name eq: '_' ;
  var name value ;
run ;

proc report data=counts ;
  column cat ("Row count" subcat,row_count) ;
  define cat / group order=data ;
  define subcat / across ' ' ;
  define row_count / analysis sum center ' ' format=comma12. ;
  rbreak after / summarize ;
  compute row_count ;
    if cat ne ' ' then return ;
    pretext = symget(cats('_items',_col_-1)) ;
    ucount  = input(symget(cats('_',_col_-1)),best8.) ; 
    style = 'style=[pretext='||quote(cats(pretext,'^{newline}]',put(ucount,comma12.))) ;
    call execute ('%put ' || style) ;
    call define (_col_, 'style', trim(style)) ;
    row_count = input(ucount, best8.) ;
  endcomp ;
  compute after ;
    cat = 'Distinct Key Count' ;
  endcomp ;
run ;

proc tabulate data=ukey_counts;
  class subcat keyitems ;
  var subcat_ukey_count ;
  table subcat * keyitems=' ' * subcat_ukey_count=' '*sum=' '*f=comma12. ;
run;
 
ods html close ;

[Cynthia_sas]

Hi:

  I see the major issue with your code as the fact that ROW_COUNT is under an ACROSS item. That means your COMPUTE block has to use absolute column numbers and the reference _col_.sum won't work.That's the reason you're getting these errors:

• ERROR: The variable type of 'ROW_COUNT.SUM'n is invalid in this context.
• ERROR: The variable type of '_COL_.SUM'n is invalid in this context.

  The "invalid in this context" is directly related to your ROW_COUNT being under SUBCAT which is an ACROSS item. You MUST use absolute column numbers.

  So as an example using your posted output, if CAT is the first column then the column for ROW_COUNT under for SUBCAT=T value  will be _c2_, as shown below:

SUBCAT=U will be _c3_, SUBCAT=V will be _c4_ and SUBCAT=W will be _c5_. This is because of the PROC REPORT "preprocessing" step that assigns absolute column numbers to any columns that are under an ACROSS item. That means your COMPUTE block must refer only to the absolute column numbers, as shown below:

  Since you only wanted to change the RBREAK row and not add another summary row, I made a character version of ROW_COUNT called CHAR_RC under each SUBCAT value and then I did all my changes to the absolute columns for CHAR_RC in the COMPUTE AFTER block. No need to use SYMGET, since this was just a one-time retrieval of the macro variable values from the Global Symbol Table. Also, I tend to avoid the pretext style override because I find that it is not always reliable across all destinations, especially when used with numeric items. So, that's the reason I made CHAR_RC and then just used NOPRINT for the final report #2.

  I'm sure you could write more complex code, including with DO Loops or even Macro %DO loops, but I used code that should be understandable to someone who's only taken Programming 1.

  Hope this helps,

Cynthia