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!
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;
@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"?
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;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.