A quick question

[Zerg]

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.

[ballardw · Accepted solution]

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.

[Zerg]

Thank you. Your code works.

[Ksharp]

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;