group by id

pacman94 — Fri, 11 Mar 2022 14:43:36 GMT

I have the following dataset

ID	Group	Age	Gender
1	1	61	Female
1	2	61	Female
2	2	62	Male
3	1	64	Female
4	1	66	Male
4	2	66	Male
5	2	64	Male
6	1	67	Female
6	2	67	Female

What I would like to do is that if same ID has 'Group = 1 and Group = 2" then I want to create a new variable for ID: final_group = Group 1 if Group = 1; final_group = 'Group 2 Indirect';

if only one record then use same final_group = Group

Something like this:

ID	Group	Final_phase	Age	Gender
1	1	Group 1	61	Female
1	2	Group 2 Indirect	61	Female
2	2	Group 2 Direct	62	Male
3	1	Group 1	64	Female
4	1	Group 1	66	Male
4	2	Group 2 Indirect	66	Male
5	2	Group 2 Direct	64	Male
6	1	Group 1	67	Female
6	2	Group 2 Indirect	67	Female

I tried to do max per patient and create additional datasets but was wondering if there is an easy way

Re: group by id

AMSAS — Fri, 11 Mar 2022 15:33:57 GMT

data have ;
	infile cards ;
	input id  group  age gender $ ;
cards ;
1	1	61	Female
1	2	61	Female
2	2	62	Male
3	1	64	Female
4	1	66	Male
4	2	66	Male
5	2	64	Male
6	1	67	Female
6	2	67	Female
;
run ;

proc sort data=have out=srtdhave ;
	by id group ;
run ;

data want ;
	retain group1Flag 0 ;
	drop group1Flag ;
	length final_phase $20 ;
	set srtdhave ;
	by id group ;
	if first.id then do ;
		group1Flag=0 ;
	end ;
	if group=1 then 
		group1Flag=1 ;
	final_phase="Group "!!left(putn(group,"8.")) ;
	if group=2 then do ;
		if group1Flag=1 then do ;
			final_phase=trim(final_phase)!!" Indirect" ;
		end ;
		else
		do ;
			final_phase=trim(final_phase)!!" Direct" ;
		end ;
	end ;
run ;

First and Last are your friend

topic group by id in SAS Programming

group by id

Re: group by id