grouping clients

[Tal]

based on the crieria below i need to get a "want" table such as this one below but     cuz of the (product_type=n and prod_code ne l)

the client with id=102 gets placed in the group=100 and he is not supposed to as he also has a code=l

data have;

input id prod_type $ prod_code $ ;

cards;

100 a b

101 a b

101 c s

102 n v

102 n l

103 n v

103 n j

105 a d

106 v d

107 g t

;

data want;

set have;

if prod_type in(a,c) or

   prod_code=d       or

   (product_type=n and prod_code ne l) then group=100 ;run;

want:

id prod_type prod_code group ;

100 a b    100

101 a b    100

101 c s    100

102 n v

102 n l

103 n v    100

103 n j    100

105 a d    100

106 v d    100

107 g t

and  ths is  what i get

id prod_type prod_code group ;

100 a b    100

101 a b    100

101 c s    100

102 n v    100

102 n l

103 n v    100

103 n j    100

105 a d    100

106 v d    100

107 g t

How do i fix this ,anyone please?

[Patrick · Accepted solution]

Below code should do:

data have;

input id prod_type $ prod_code $ ;

cards;

100 a b

101 a b

101 c s

102 n v

102 n l

103 n v

103 n j

105 a d

106 v d

107 g t

;

run;

%let cond=prod_code='d' or prod_type in ('a','c') or (prod_type='n' and prod_code ne 'l');

data want(drop=_:);

  set have;

  if _n_=1 then

    do;

      dcl hash h1(dataset:"have(where=(not (&cond)))");

      _rc=h1.defineKey('id');

      _rc=h1.defineDone();

    end;

  if h1.check() ne 0 then group=100;

run;

[Tal]

dont know hash tables but this really works

Thanks a lot Patrick