topic Re: grouping the records in SAS Procedures

grouping the records

sam369 — Thu, 19 Mar 2015 04:59:24 GMT

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

101 test1

102 test1

103 test1

;

run;

want :

grp chk1 chk2 chk3

test1 3 1 0

Thanks

Sam

Re: grouping the records

gergely_batho — Thu, 19 Mar 2015 07:59:49 GMT

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ó

Re: grouping the records

chrej5am — Thu, 19 Mar 2015 08:04:17 GMT

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