how to delete row observations

[lixuan]

hello

my data like this 

data new;
input company$ forecast yymm;
cards;
A 1 198503
A 1 195503
A 0 198503
A 0 198504
A 1 198504
B 1 198503
B 1 198503
B 1 198504
C 0 198504
C 1 198504
;
run;

There is no missing data. I would like to every  observation in every date has  the variable '0,1'. If can not, the observation should be deleted. For example, A in  198503 and in 198504 which both have variable ‘0&1’  should be kept, while B in 198504 which only has variable '0'  should be deleted. So the result should only remain observation A. 

Thanks a lot.

[art297]

Here is one way to do it:

data want;
  do until (last.company);
    set new;
    by company;
    if first.company then do;
      _have0=0;
      _have1=0;
    end;
    if forecast=1 then have1=1;
    else if forecast=0 then have0=1;
  end;
  do until (last.company);
    set new;
    by company;
    if have0 and have1 then output;
  end;
run;

HTH,

Art, CEO, AnalystFinder.com

[ChrisNZ · Accepted solution]

Another way:

proc sql; 
  select src.*
    from HAVE          src
        ,(select COMPANY, YYMM, count(unique(FORECAST)) as C
          from HAVE
          group by 1,2
          having C=2)  vet
    where src.COMPANY=vet.COMPANY 
        & src.YYMM   =vet.YYMM;
quit;

[lixuan]

Hi, thank you very much , but C should also be deleted, because C don't have '0&1' in 198503. Thanks again.

[lixuan]

Also, all observations of A should be kept. Thanks 

[ChrisNZ]

In this case, you havent explained you logic properly.

Are you saying that company A should be kept because it includes, somewhere in its records, all 4 dates and all 2 forecasts? regardless of any crossing between date and forecast?

If so, how do we get the list of all dates to check against in order for a company to be kept?

[lixuan]

I really appreciate all of you. My problem has been resolve. Thank you 

[art297]

c had 2 records, one with a 1, and the 2nd with a 0. That is why the code kept both a and c.

Art, CEO, AnalystFinder.com