BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Zerg
Calcite | Level 5

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Calcite | Level 5

Thank you. Your code works.

Ksharp
Super User
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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 847 views
  • 0 likes
  • 3 in conversation