topic Re: Deduce a dataset in SAS Enterprise Guide

Deduce a dataset

shubham_d — Mon, 30 Jan 2023 14:56:47 GMT

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

Re: Deduce a dataset

PaigeMiller — Mon, 30 Jan 2023 15:09:41 GMT

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;

Re: Deduce a dataset

shubham_d — Mon, 30 Jan 2023 15:35:39 GMT

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

Re: Deduce a dataset

PaigeMiller — Mon, 30 Jan 2023 15:56:49 GMT

@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.

Re: Deduce a dataset

ballardw — Mon, 30 Jan 2023 16:02:45 GMT

Are there always exactly three records per Id?

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

Re: Deduce a dataset

jebjur — Mon, 30 Jan 2023 16:31:57 GMT

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;

Re: Deduce a dataset

shubham_d — Mon, 30 Jan 2023 17:29:09 GMT

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!!

Re: Deduce a dataset

shubham_d — Mon, 30 Jan 2023 17:31:00 GMT

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.

Re: Deduce a dataset

shubham_d — Fri, 31 Mar 2023 12:05:37 GMT

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.