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
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
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
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.