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 ;
... View more