topic Re: How to create binary variables from multiple observations? in New SAS User

How to create binary variables from multiple observations?

dac_js — Tue, 18 Jan 2022 17:58:19 GMT

I have the following data:

data have;
input
id var_1 $ var_2 flag_1 flag_2;
datalines;
1 ab 1 0 1
1 ab 1 0 1
1 ab 1 0 0
1 ab 1 0 0
1 ef 7 1 0
1 ef 7 0 0
1 ef 7 1 0
2 ef 3 1 1
2 xy 4 1 0
2 xy 4 1 0
2 xy 4 1 0
2 xy 5 0 1
2 xy 5 0 1
;
run;

I want to create a table like below:

ID	Var_1	Var_2	Flag_1_new	Flag_2_new
1	ab	1	0	1
1	ef	7	1	0
2	ef	3	1	1
2	xy	4	1	0
2	xy	5	0	1

Here for each ID, Var_1, and Var_2 combination, I am trying to create one Flag_1_new and one Flag_2_new. The value of this Flag_1_new will be 1 if any of the observation for that group has a flag_1 value of 1 (else 0). Need to repeat the same for Flag_2_new.

I hope someone can help me to find a solution. Thanks in advance!

Re: How to create binary variables from multiple observations?

PaigeMiller — Tue, 18 Jan 2022 18:00:31 GMT

@dac_js wrote:

Here for each ID, Var_1, and Var_2 combination, I am trying to create one Flag_1_new and one Flag_2_new. The value of this Flag_1_new will be 1 if any of the observation for that group has a flag_1 value of 1 (else 0).

What do you mean by "group"?

Re: How to create binary variables from multiple observations?

novinosrin — Tue, 18 Jan 2022 18:04:58 GMT


data have;
input
id var_1 $ var_2 flag_1 flag_2;
datalines;
1 ab 1 0 1
1 ab 1 0 1
1 ab 1 0 0
1 ab 1 0 0
1 ef 7 1 0
1 ef 7 0 0
1 ef 7 1 0
2 ef 3 1 1
2 xy 4 1 0
2 xy 4 1 0
2 xy 4 1 0
2 xy 5 0 1
2 xy 5 0 1
;
run;


proc sql;
  create table want as
  select id, var_1, var_2, max(flag_1) as flag_1_new,
         max(flag_2) as flag_2_new
  from have
  group by id, var_1, var_2;
quit;

Re: How to create binary variables from multiple observations?

dac_js — Tue, 18 Jan 2022 19:41:16 GMT

Thank you for your response. I was considering each combination of ID, var_1 and var_2 as group.

Re: How to create binary variables from multiple observations?

dac_js — Tue, 18 Jan 2022 19:43:50 GMT

Thank you very much!