The logic is the following: I might have company where profit information is given like this from 1998-2001 (COid 892) : -, ., +, +. If the company has any two years of negative profit prior to 2001 (not necessarily consecutive), then MorethanTwo will be 1. In Company 892 case, there is two years of profit information is given prior to 2001 but they are not consecutive and they are not both negative. Therefore MorethanTwo will 0, while TwoinRow and ThreeinRow will be missing. Can you please help me with the code that will give a variable called "MorethanTwo" if there are two years (not consecutive) prior to 2001 that will give me 1 if the profit is negative, 0 if there is at least two years of information but they are not negative, and missing (.) if I don't have at least two years of information on profit prior to 2001? Hope this clarifies the confusion. I thought that my original proc sql code does that. But for some reason, it does not. Here is the code: proc sql; create table want as select *, case when (select count(*) from have where coid=a.coid and year le 2000 and profit lt 0) gt 1 then 1 else 0 end as MorethanTwo from have as a group by coid ; quit; Thanks, Therock
... View more