topic Re: exclude a group of observations in terms of one observation in SAS Programming

exclude a group of observations in terms of one observation

France — Mon, 12 Nov 2018 12:07:52 GMT

Dear all,

how can exclude a group of observations in terms of one observation?

for example, in following observations,

psn_name_cited, docdb_family_id, psn_name_citing 

apple,1,pear

apple,1,apple

apple,2,orange 

apple,2,banana

apple,2, pear

juice,3, bottle

orange,3,water

I would like to exclude the following observations

apple,1,pear

apple,1,apple

by using the following codes,

PROC SQL;
   CREATE TABLE want  AS
      SELECT
       *
      FROM
        tryy
      GROUP BY psn_name_cited,docdb_family_id
      Having psn_name_cited ne psn_name_citing /* exclude self_citation */
;
QUIT;

However, only

apple,1,apple

been excluded from the sample.

Could you please give me some suggestions about this?

Thanks in advance.

Re: exclude a group of observations in terms of one observation

RW9 — Mon, 12 Nov 2018 12:21:11 GMT

Maybe joining psn_name_citing back on by psn_name_citing=psn_name, something like:

proc sql;
  create table want as
  select a.*
  from   have a
  left join have b
  on      a.psn_name=b.psn_name_citating
  where b.psn_name_citing="";
quit;

Not tested, post test data in the form of a datastep in future.

Re: exclude a group of observations in terms of one observation

Ksharp — Mon, 12 Nov 2018 13:22:12 GMT

PROC SQL;
   CREATE TABLE want  AS
      SELECT
       *
      FROM
        tryy
      GROUP BY psn_name_cited,docdb_family_id
      Having sum(psn_name_cited = psn_name_citing)=0 /* exclude self_citation */
;
QUIT;