grouping the records

[sam369]

Hi All,

I have a dataset with id and grp. I need to create 3 variables chk1,chk2 & chk3.

Criteria for chk1 : same grp with with unique subjects(from below example : 101,102,103) will fall under this

                  chk2:  same grp with more than 2 or 3 times for same subject(from below example : 100 have got multiple time of same grp)

                  chk3: same grp with more then 4 time (From below example: no subject satisfy this one so chk3 is 0)

data have;

input id $  grp $;

cards;

100   test1

100   test1

101   test1

102   test1

103   test1

;

run;

want :

grp       chk1    chk2     chk3

test1      3           1          0

Thanks

Sam

[gergely_batho]

Hi Sam,

Just to check I understand it correctly:

chk1: number of distinct IDs in the group

chk2: this is a binary variable. chk2=1 if there exists an ID in the group, with more then 2 occurrences.

chk3: this is a binary variable. chk3=1 if there exists an ID in the group, with more then 4 occurrences.

OK, now I understand.

Message was edited by: Gergely Bathó

[chrej5am]

Hi Sam,

for example create one table such as

proc sql;

create table one as

select

  grp,

  id,

  count(*) as N

  from have group by 1,2

;

quit;

and then the result

proc sql;

create table result as

select grp,

  sum(case when N=1 then 1 else 0 end) as chk1,

  sum(case when N=2 or N=3 then 1 else 0 end) as chk2,

  sum(case when N>3 then 1 else 0 end) as chk3

from one

group by 1;

quit;

/***************************************************************************/

or all in one step

proc sql;

create table result as

select grp,

  sum(case when N=1 then 1 else 0 end) as chk1,

  sum(case when N=2 or N=3 then 1 else 0 end) as chk2,

  sum(case when N>3 then 1 else 0 end) as chk3

from

  (select

  grp,

  id,

  count(*) as N

  from have group by 1,2)

group by 1;

quit;

BR,

Jakub