Grouping data

[West26]

Hello All,

I need small help with grouping of data. Have two variables (id,desc), with below values

id   desc
01   abc
01   def
01   ghi
01   xyz
02  def
02  ghi
02  xyz
03  lkm
03  ghi
03  opq
03  rst
04  abc
04  opq
04  xyz

I want group id's having desctiptoin only 'def', means

01 def
02 def

in one dataset

and others in seperate data set.

01 abc
01 ghi
01 xyz
02 ghi
02 xyz
03 lkm
03 ghi
03 opq
03 rst
04 abc
04 opq
04 xyz

Would like to know how to do this with both Proc sql and datastep(first. and last.)

Thank you.

[japelin · Accepted solution]

I don't use either "first." or "last." but how about this simple way of writing?

data have;
  length id $2 desc $3;
  input id $ desc $;
cards;
01  abc
01  def
01  ghi
01  xyz
02  def
02  ghi
02  xyz
03  lkm
03  ghi
03  opq
03  rst
04  abc
04  opq
04  xyz
;
run;

data one two;
  set have;
  if desc='def' then output one;
  else output two;
run;

proc sql;
  create table one as
  select * from have
  where desc='def';

  create table two as
  select * from have
  where desc^='def';

quit;

[West26]

Thank you, but I need to reframe my question.

DATASET TWO, has to have only below data (exclude those id's from dataset one, having description 'def')

DATA SET TWO should be like this
02 ghi
02 xyz
03 lkm
03 ghi
03 opq
03 rst

[japelin]

If you say "exclude those id's from dataset one, having description 'def'", isn't it the following data?

03 lkm
03 ghi
03 opq
03 rst
04 abc
04 opq
04 xyz