Deduce a dataset

[shubham_d]

HAVE - 

ID	NAME	SCORE	CODE
1	John	21	ABC
1	John	22	XYZ
1	John	23	ABC
2	Mark	4	XYZ
2	Mark	6	XYZ
2	Mark	5	ABC

WANT - If there is an ID with the same code, I want to keep the row that has a higher score & drop the one with a lower score. PS - We have to deduce this ID-wise. It's okay to have the same CODE across different IDs. 

ID	NAME	SCORE	CODE
1	John	22	XYZ
1	John	23	ABC
2	Mark	6	XYZ
2	Mark	5	ABC

[PaigeMiller · Accepted solution]

UNTESTED CODE (please provide your data as SAS data step code if you want tested code)

proc sort data=have;
    by id code score;
run;
data want;
    set have;
    by id code;
    if last.code;
run;

[shubham_d]

Data -

data have;
input id name $ score code $;
datalines;
1 John 21 ABC
1 John 22 XYZ
1 John 21 ABC
2 Mark 4 XYZ
2 Mark 6 XYZ
2 Mark 5 XYZ
;
run;

Tried running your code & got an empty data set

[PaigeMiller]

---

@shubham_d wrote:
Data -

data have;
input id name $ score code $;
datalines;
1 John 21 ABC
1 John 22 XYZ
1 John 21 ABC
2 Mark 4 XYZ
2 Mark 6 XYZ
2 Mark 5 XYZ
;
run;

Tried running your code & got an empty data set

---

Works for me. It is never helpful to say something didn't work, and not provide details. In this case, we need to see the complete log.

[shubham_d]

Hey! My bad, will keep this in mind as I interact more in this community. By the way, the code worked fine! Not sure why I did not receive the results in the first run. Thanks a lot!!

[shubham_d]

Hi Paige, 

If the score is the same, I would like to maintain the order of "Code" as it is in Have.

Facing an issue in a scenario where my data is - 

HAVE - 

data have;
input id name $ score code $;
datalines;
1 John 23 A05
1 John 26 A02
1 John 26 A01
2 Mark 21 A05
2 Mark 22 A05
2 Mark 23 A07
;
run;

ID	NAME	SCORE	CODE
1	John	23	A05
1	John	26	A02
1	John	26	A01
2	Mark	21	A05
2	Mark	22	A05
2	Mark	23	A07

WANT - 

ID	NAME	SCORE	CODE
1	John	26	A02 [ Same Order as Input ]
1	John	26	A01
1	John	23	A05
2	Mark	22	A05
2	Mark	23	A07

Using the solution you provided, the A01 is getting sorted & coming at the top, whereas I want to keep the order as it is when the score is equal. 

[ballardw]

Are there always exactly three records per Id?

What do you want if there is only one record? Two?

[shubham_d]

Hey! No, the records will vary for each Id. If the records are unique with respect to code, I want to keep them as it is.

[jebjur]

This code works for the sample data you provided:

data have;
input id name $ score code $;
datalines;
1 John 21 ABC
1 John 22 XYZ
1 John 23 ABC
2 Mark 4 XYZ
2 Mark 6 XYZ
2 Mark 5 ABC
;
run;

proc sort data=have;
by id code score;
run;

data want;
set have;
by id code score;
if last.code;
run;