turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- grouping the records

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-19-2015 12:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sam369

03-19-2015 03:59 AM

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ó

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sam369

03-19-2015 04:04 AM

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