I have a dataset looks like:
id date var1 var2
1 2000 0 1
1 2000 0 1
1 2000 1 1
1 2001 0 0
1 2001 0 0
1 2001 0 0
2 2001 0 0
2 2001 0 0
2 2001 0 0
2 2001 0 0
2 2002 0 1
2 2002 0 1
2 2002 1 1
I want var2 equal to 1 as long as var1 in an id-date group has a value of 1. The result I want is shown in the example data as well. How can I resolve this? Thank you.
Does VAR2 already exist or are we supposed to add it?
Please post existing data in the form of a data step as shown below:
data have; input id date var1 var2; datalines; 1 2000 0 1 1 2000 0 1 1 2000 1 1 1 2001 0 0 1 2001 0 0 1 2001 0 0 2 2001 0 0 2 2001 0 0 2 2001 0 0 2 2001 0 0 2 2002 0 1 2 2002 0 1 2 2002 1 1 ; proc sql; create table want as select a.id, a.date, a.var1,b.var2 from have as a left join (select id,date, max(var1) as var2 from have group by id,date) as b on a.id=b.id and a.date=b.date ; run;
This assumes that Var1 will never have a value larger than 1 since you did not show any.
Basic approach when setting values across a group is 1) get a summary that has the desired value then 2) join/ merge/ update back with the first data.
Does VAR2 already exist or are we supposed to add it?
Please post existing data in the form of a data step as shown below:
data have; input id date var1 var2; datalines; 1 2000 0 1 1 2000 0 1 1 2000 1 1 1 2001 0 0 1 2001 0 0 1 2001 0 0 2 2001 0 0 2 2001 0 0 2 2001 0 0 2 2001 0 0 2 2002 0 1 2 2002 0 1 2 2002 1 1 ; proc sql; create table want as select a.id, a.date, a.var1,b.var2 from have as a left join (select id,date, max(var1) as var2 from have group by id,date) as b on a.id=b.id and a.date=b.date ; run;
This assumes that Var1 will never have a value larger than 1 since you did not show any.
Basic approach when setting values across a group is 1) get a summary that has the desired value then 2) join/ merge/ update back with the first data.
Thank you. Your code works.
data have;
input id date var1 ;
datalines;
1 2000 0 1
1 2000 0 1
1 2000 1 1
1 2001 0 0
1 2001 0 0
1 2001 0 0
2 2001 0 0
2 2001 0 0
2 2001 0 0
2 2001 0 0
2 2002 0 1
2 2002 0 1
2 2002 1 1
;
proc sql;
create table want as
select *,max(var1) as var2
from have
group by id,date;
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.