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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.