Use COUNT DISTINCT to count the number of unique values in a column.
FYI - I'm assuming you want the final code to be GROUP# + DIRECT, ie 3 Direct if 3 was the maximum. Your data doesn't show a 3 but if it was possible this still works.
EDIT: Modified to use calculated key word and only display results for the max group records.
proc sql;
select *, max(group) as max_group,
case when count(distinct group) > 1 and group = max_group then catt(calculated max_group, " Direct")
else if group = calculated max_group then calculated max_group
else " "
end as new_group
from data;
group by id;
quit;
@pacman94 wrote:
I have the following data
| ID |
Date |
group |
| 1 |
1/2/2021 |
1 |
| 1 |
1/3/2021 |
2 |
| 2 |
1/2/2021 |
2 |
| 3 |
1/5/2021 |
2 |
| 4 |
1/4/2021 |
1 |
| 4 |
1/5/2021 |
2 |
What I would like to do is that
for each ID, I need to take the max group and add additional groups
if ID only has one instance then keep that new_group as is
if the ID has more than one instance then new_group = '2 Indirect'
like this
then only keep the new group
| ID |
Date |
group |
new_group |
| 1 |
1/2/2021 |
1 |
|
| 1 |
1/3/2021 |
2 |
2 Indirect |
| 2 |
1/2/2021 |
2 |
2 |
| 3 |
1/5/2021 |
2 |
2 |
| 4 |
1/4/2021 |
1 |
|
| 4 |
1/5/2021 |
2 |
2 Indirect |
i did the following
proc sql;
select *, max(group) as group
from data;
group by id;
quit;
