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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.