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