topic Re: A quick question in SAS Programming

A quick question

Zerg — Wed, 22 May 2019 21:27:00 GMT

I have a dataset looks like:

id date var1 var2

1 2000 0 1

1 2000 1 1

1 2001 0 0

2 2001 0 0

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.

Re: A quick question

ballardw — Wed, 22 May 2019 21:40:24 GMT

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.

Re: A quick question

Zerg — Wed, 22 May 2019 21:54:08 GMT

Thank you. Your code works.

Re: A quick question

Ksharp — Thu, 23 May 2019 11:47:39 GMT

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;