data have;
input ID Year Indicator;
cards;
1 1991 1
1 1992 0
1 1992 1
2 1993 2
3 1991 0
3 1991 1
4 1991 .
;
run;
proc sql;
select a.id,a.year,sum(Indicator=0) as No0,sum(Indicator=1) as No1,sum(Indicator=2) as No2
from (select * from (select distinct id from have),
(select distinct year from have)) as a
left join have as b
on a.id=b.id and a.year=b.year
where catx(' ',a.id,a.year) not in (select catx(' ',id,year) from have where indicator is missing)
group by a.id,a.year
union
select id,year from have where indicator is missing;
quit;
... View more
