topic Grouping data in SAS Programming

Grouping data

West26 — Tue, 20 Sep 2022 02:52:22 GMT

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.

Re: Grouping data

japelin — Tue, 20 Sep 2022 03:00:21 GMT

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;

Re: Grouping data

West26 — Tue, 20 Sep 2022 03:14:49 GMT

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

Re: Grouping data

japelin — Tue, 20 Sep 2022 03:26:01 GMT

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