Hi everyone.
I have the following data step:
data file;
input ID $ group;
cards;
1 0
1 1
2 0
3
3 0
4 1
5
;
run;
I would like to recode the var group. If an ID has a duplicate (eg. 1 and 3), the row value will take on the maximum group.
The column would look like
ID Group Group_r
1 0 1
1 1 1
2 0 0
3 0
3 0 0
4 1 1
5
Thanks.
Methinks the simplest is:
data have ;
input ID $ group ;
cards ;
1 0
1 1
2 0
3 .
3 0
4 1
5 .
;
run ;
proc sql ;
create table want as select *, max (group) as group_r from have group id ;
quit ;
If you want the DATA step instead, could be:
data want ;
set have (in=h1) have (in=h2) ;
by id ;
retain group_r ;
if first.id then group_r = . ;
if h1 then group_r = group_r max group ;
if h2 ;
run ;
The alternative below is a bit more verbose, but it may make more sense logically (depending on whom you ask):
data want ;
do _n_ = 1 by 1 until (last.id) ;
set have ;
by ID ;
group_r = group_r max group ;
end ;
do _n_ = 1 to _n_ ;
set have ;
output ;
end ;
run ;
Kind regards
Paul D.
data file;
input ID $ group;
cards;
1 0
1 1
2 0
3 .
3 0
4 1
5 .
;
run;
proc sql;
create table want as
select *,max(group) as group_r
from file
group by id;
quit;
Methinks the simplest is:
data have ;
input ID $ group ;
cards ;
1 0
1 1
2 0
3 .
3 0
4 1
5 .
;
run ;
proc sql ;
create table want as select *, max (group) as group_r from have group id ;
quit ;
If you want the DATA step instead, could be:
data want ;
set have (in=h1) have (in=h2) ;
by id ;
retain group_r ;
if first.id then group_r = . ;
if h1 then group_r = group_r max group ;
if h2 ;
run ;
The alternative below is a bit more verbose, but it may make more sense logically (depending on whom you ask):
data want ;
do _n_ = 1 by 1 until (last.id) ;
set have ;
by ID ;
group_r = group_r max group ;
end ;
do _n_ = 1 to _n_ ;
set have ;
output ;
end ;
run ;
Kind regards
Paul D.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.