BookmarkSubscribeRSS Feed
sam369
Obsidian | Level 7

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

2 REPLIES 2
gergely_batho
SAS Employee

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 693 views
  • 0 likes
  • 3 in conversation