If someone knows why...
I answer my own question:
Testing c.ORIGIN and c.TYPE assesses observation-level values while testing calculated M and calculated N assesses summarised values.
Adding a presence flag to see whether a record is prsent computes the counts properly:
data HAVE;
retain FLG 1;
set SASHELP.CARS;
if mod(_N_,5) = 0 then LENGTH=.;
if ORIGIN='Asia' and TYPE='SUV' then LENGTH=.;
run;
proc sql;
create table COUNTS as
select a.ORIGIN
, b.TYPE as CARTYPE
, sum(LENGTH is missing) as M
, count(*) as N
, 'miss' as FMTNAME
, 'N' as TYPE
, monotonic() as OBS
, case when sum(FLG) < 1
then 'No records'
else catx(' ', calculated M, 'missing out of' , calculated N, '=', put(calculated M /calculated N, percent.) )
end as LABEL
from (select unique ORIGIN from HAVE) a
full outer join
(select unique TYPE from HAVE) b
on 1
left join
HAVE c
on a.ORIGIN = c.ORIGIN
and b.TYPE = c.TYPE
group by 1, 2;
quit;
proc format cntlin=COUNTS(rename=(OBS=START));
run;
proc report data=COUNTS;
columns CARTYPE ORIGIN, OBS;
define CARTYPE / group ;
define ORIGIN / across;
define OBS / sum format=miss. ' ';
run;
... View more
