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.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: