topic Re: how to delete row observations in SAS Programming

how to delete row observations

lixuan — Thu, 09 Mar 2017 03:26:08 GMT

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.

Re: how to delete row observations

art297 — Thu, 09 Mar 2017 03:32:51 GMT

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

Re: how to delete row observations

ChrisNZ — Thu, 09 Mar 2017 03:59:26 GMT

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;

Re: how to delete row observations

lixuan — Thu, 09 Mar 2017 04:08:44 GMT

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

Re: how to delete row observations

lixuan — Thu, 09 Mar 2017 04:15:11 GMT

Also, all observations of A should be kept. Thanks

Re: how to delete row observations

ChrisNZ — Thu, 09 Mar 2017 04:45:47 GMT

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?

Re: how to delete row observations

art297 — Thu, 09 Mar 2017 05:46:10 GMT

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

Re: how to delete row observations

lixuan — Fri, 10 Mar 2017 16:32:25 GMT

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