Unless I am missing something the SQL solution is simple.
The max of N1 being 0 would mean all obs are 0, it being 1 would mean at least one was 1. The only problem I see with this solution is in the case of duplicate values of N3 or N4.
proc sql;
create table new as select a.id, a.year, n1,n2,n3,n4 from
( select *,
max(n1) as m1, max(n2) as m2, max(n3) as m3
from
have
group by id, year) a
left join
(select id, year, max(n2) as o2 from (select * from have where n1 = 1) group by id, year )b
on a.id = b.id and a.year = b.year
where (a.m1 = 1 and a.n1 = a.m1 and a.n2 = b.o2) or (a.m1= 0 and a.n3=a.m3)
;
quit;
Oops missing one thing here
Message was edited by: Flip
This should do it, I missed the apart about N1 = 1 for the solution.
Message was edited by: Flip
... View more
