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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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


Accepted Solutions
Solution
‎12-17-2017 12:09 PM
PROC Star
Posts: 7,807

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

Posted in reply to MB_Analyst

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

 

View solution in original post


All Replies
Solution
‎12-17-2017 12:09 PM
PROC Star
Posts: 7,807

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

Posted in reply to MB_Analyst

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

 

PROC Star
Posts: 834

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

Posted in reply to MB_Analyst

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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