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
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.