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
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ó
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
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.
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.