topic Re: Find if a group contains a value to create new variable (SAS 9.4) in SAS Programming

Find if a group contains a value to create new variable (SAS 9.4)

MB_Analyst — Sun, 17 Dec 2017 16:39:35 GMT

I have a data set with a grouping variable (grp_id). For each observation I want to create a new variable to specify whether that obs. fulfils criteria individually and if the GROUP fulfils criteria. The data looks like this:

grp_id binary grp_crit
1 0 1
1 0 6
1 1 7
2 0 0
2 1 9
2 0 3
3 1 2
3 0 4
3 0 0

For each group I want a new variable that is 1 IF binary==1 AND the group_crit has a 0 anywhere in it, ELSE the var_new would be 0. To give the result:

grp_id binary grp_crit var_new

1 0 1 0

1 0 6 0

1 1 7 0

2 0 0 0

2 1 9 1

2 0 3 0

3 1 2 1

3 0 4 0

3 0 0 0

Re: Find if a group contains a value to create new variable (SAS 9.4)

art297 — Sun, 17 Dec 2017 16:57:08 GMT

Here is one way:

data want (drop=met);
  do until (last.grp_id);
    set have;
    by grp_id;
    if first.grp_id then met=0;
    if grp_crit eq 0 then met=1;
  end;
  do until (last.grp_id);
    set have;
    by grp_id;
    if met and binary eq 1 then var_new=1;
    else var_new=0;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

Re: Find if a group contains a value to create new variable (SAS 9.4)

novinosrin — Sun, 17 Dec 2017 18:49:51 GMT

Since, it's been answered, just for fun:

data have;

input grp_id binary grp_crit;

datalines;

1 0 1

1 0 6

1 1 7

2 0 0

2 1 9

2 0 3

3 1 2

3 0 4

3 0 0

;

proc sql;

create table want as

select grp_id, binary, grp_crit, (binary=1 and sum(grp_crit=0)) as var_new

from have

group by grp_id;

quit;