final group value

pacman94 — Wed, 10 Mar 2021 18:51:12 GMT

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;

Re: final group value

Reeza — Wed, 10 Mar 2021 21:25:22 GMT

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;

topic Re: final group value in SAS Programming

final group value

Re: final group value