topic Deleteing observation with equal variable as one with certain conditions in SAS Programming

Deleteing observation with equal variable as one with certain conditions

Viktoreli — Fri, 04 Oct 2019 07:10:51 GMT

Hi,

I could not find a solution for this problem on the board so I thought I would ask instead.

As the long subject line says,

If a variable A meets certain conditions then I want to delete all observations with the same variable B as the other observation.

If A >10 and B is 5, then I want to remove all variables where B=5.

I want this

Obs A B

1 5 10

2 11 10

3 1 11

4 9 11

5 12 12

6 3 13

To become this

1 1 11

2 9 11

3 3 13

Thanks in advance!

Re: Deleteing observation with equal variable as one with certain conditions

PeterClemmensen — Fri, 04 Oct 2019 07:19:34 GMT

I don't see one obs where B=5?

Re: Deleteing observation with equal variable as one with certain conditions

Viktoreli — Fri, 04 Oct 2019 07:44:17 GMT

Should have added that in my lines,
b=5 was just an example of a value.
I was not clear enough in what I was asking.

If an observation meets certain conditions in variable A,
I want to remove all observations with the same variable B as the observation that met the conditions for variable A.

Re: Deleteing observation with equal variable as one with certain conditions

Patrick — Fri, 04 Oct 2019 07:51:39 GMT

Here you go:

data have;
  input a b;
  datalines;
5 10
11 10
1 11
9 11
12 12
3 13
;

data want;
  if _n_=1 then
    do;
      dcl hash h1(dataset:'have(where=(a>10))');
      h1.defineKey('b');
      h1.defineDone();
    end;
  set have;
  if h1.check()=0 then delete;
run;

proc print data=want;
run;

Re: Deleteing observation with equal variable as one with certain conditions

PeterClemmensen — Fri, 04 Oct 2019 08:12:46 GMT

Ok. I would go with the solution by @Patrick then.

Alternatively you could do

data have;
  input a b;
  datalines;
5 10
11 10
1 11
9 11
12 12
3 13
;

data want;
    array _ {1000000} _temporary_;
    do until (lr1);
        set have(where=(a>10)) end=lr1;
        _[b]=1;
    end;

    do until (lr2);
        set have end=lr2;
        if _[b] ne 1 then output;
    end;
    stop;
run;

Re: Deleteing observation with equal variable as one with certain conditions

Viktoreli — Fri, 04 Oct 2019 08:48:31 GMT

Worked great and yielded the same result as patricks!
Im very grateful!

Re: Deleteing observation with equal variable as one with certain conditions

Viktoreli — Fri, 04 Oct 2019 08:48:58 GMT

Thanks alot!