Hi Try this , data test; input year firm :$ industry :$ value; if value>0 then Status='A' ;else Status='N'; cards; 2000 a I 8 2000 b II 1 2001 a I 7 2001 a I 0 2001 b II 6 2002 a I 5 2002 a I 0 2002 b II 5 2003 b II 2 2003 c III 9 2003 d III 0 ; proc sql; /*query3:gives count of firms per year*/ select year,count(*) as count_firm from ( /* query2:gives count of firms per year per industry wise having status ='A' */ select year,firm,industry,status,count(*) as A_firms from ( /* query1 start: gives solution to your point2*/ select distinct year,firm,industry,value,status from test group by year,firm having value=max(value) /*query1 ends*/ ) where status='A' group by year,industry /*query2 ends*/ ) group by year; /*query3 ends*/ quit;
... View more