09-11-2014 12:16 PM
Hi, this question seems to be easy, but I just don't know how to do. I have the following data
permno month return i
1 1 0.1 1
1 2 0.1 .
2 1 0.5 .
2 2 0.6 1
2 3 0.2 .
3 1 0.5 .
Let me explain. permno is an ID number, month the number of month, it corresponds to something like Aug, 2000, etc.
i is an identification number.
I marked i=1 for something I want to use to compare. First, I wan to choose the month when i=1. In this case, month 1 and 2. Then I would like to choose any observation with permno NOT equal to 1 or 2 (That i identifies) AND month equal to 1 and 2 (That i identifies). Calculate the average of return, compare this average return to the average of returns, of which i=1 identifies. That is permno 3 with month 1(the last observation) meets my requirement. I would like to mark i as 0 for this observation. The average mean is 0.5 (one number). The average return of i=1 is (0.1+0.6)/2=0.35.
I basicly want to mark down the last observation with i=0. How to do?
09-11-2014 01:19 PM
create table want as
select permno,month,return,case when i=. then i=0 end as i from have
where permno not in (select permno from have where i=1 or
month not in (select month from have where i=1))
select * from have where i=1;
proc means data=want;
09-12-2014 09:54 AM
data have; input permno month return i ; cards; 1 1 0.1 1 1 2 0.1 . 2 1 0.5 . 2 2 0.6 1 2 3 0.2 . 3 1 0.5 . ; run; data want; if _n_ eq 1 then do; if 0 then set have; declare hash ha1(dataset:'have(where=(i=1))'); ha1.definekey('permno'); ha1.definedone(); declare hash ha2(dataset:'have(where=(i=1))'); ha2.definekey('month'); ha2.definedone(); end; set have; if ha1.check() ne 0 and ha2.check() eq 0 then i=0; run;