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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.