topic Re: How to select the distinct rows with when variables are not one-to-one with group by variable in SAS Programming

How to select the distinct rows when variables are not one-to-one with 'group by' variable

Cruise — Mon, 12 Nov 2018 22:18:39 GMT

Hi All,

I'd like to:

- Select the row associated with the smallest diff_range

- Among the patients who took diagnosis codes 1 or 2 or 6

- Select the row associated with the distinct smallest diff_range

The want is below and for example why ID=1 was selected was that:

ID=1 took target diagnosis codes 1 and 2. But 1, 1, 2, row is selected because of minimum value for a diff_range.

My current codes produce more observations than I need because code and diff_range variables are not one-to-one with the ID and min_diff. But I really don't want min_diff repeated in the final want data and also not willing to deduplicate data using extra step afterwards.

Any solutions? please. I'll greatly appreciate your help.

data p.have;
input id code diff_range;
cards;
1 1 2
1 2 3
1 3 4
1 3 4
1 5 7
2 1 1
2 2 3
2 2 3
2 4 5
2 5 6
3 3 1
3 4 2
3 5 3
4 8 8
4 9 9
5 6 10
5 10 12
6 2 2
6 2 1
6 6 9
6 6 4
;

data want;
input id code diff_range;
cards;
1 1 2
2 1 1
5 6 10
6 2 1
;

proc sql;
create table p.want_min1 as
select distinct * , min(diff_range) as min_diff from p.have
where id in
(select id from p.have where code in (1,2,6))
group by id;
quit;

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

r_behata — Mon, 12 Nov 2018 17:04:01 GMT

data have;
input id code diff_range;
cards;
1 1 2
1 2 3
1 3 4
1 3 4
1 5 7
2 1 1
2 2 3
2 2 3
2 4 5
2 5 6
3 3 1
3 4 2
3 5 3
4 8 8
4 9 9
5 6 10
5 10 12
6 2 2 
6 2 1
6 6 9
6 6 4
;
run;

proc sort data=have(where=(code in (1,2,6)));
	by id diff_range;
run;

data want;
	set have;
	by id diff_range;

	if first.id then output;
run;

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

novinosrin — Mon, 12 Nov 2018 17:06:48 GMT

That's happening because of remerging behaviour of proc sql

try this change highlighted

proc sql;
create table p.want_min1 as
select distinct * , min(diff_range) as min_diff from p.have
where id in
(select id from p.have where code in (1,2,6))
group by id

having diff_range=min_diff;
quit;

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

novinosrin — Mon, 12 Nov 2018 17:34:51 GMT

One question you would have to let know is what if there is a tie in min_diff?

And if you do not want min_diff in your output, the code can be as simple as

data have;
input id code diff_range;
cards;
1 1 2
1 2 3
1 3 4
1 3 4
1 5 7
2 1 1
2 2 3
2 2 3
2 4 5
2 5 6
3 3 1
3 4 2
3 5 3
4 8 8
4 9 9
5 6 10
5 10 12
6 2 2 
6 2 1
6 6 9
6 6 4
;

proc sql;
create table want_min2 as
select  *  from have
where id in 
(select id from have where code in (1,2,6))
group by id

having diff_range= min(diff_range);
quit;

proc print noobs;run;

Results:

SAS Output

The SAS System

id	code	diff_range
1	1	2
2	1	1
5	6	10
6	2	1

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

Reeza — Mon, 12 Nov 2018 18:57:56 GMT

Sort and use FIRST.

Proc sort data= have;
By Id diff_range code;
Run;

Data want;
Set have; 
By id diff_range;
Where code in (1, 2, 6);

If first.id;

Run;

This will not keep duplicates. You could also sort by code to ensure you always get the smallest or largest. Though this may not be necessary, it’s easier to have a consistent logic so that you can replicate this if ever needed.

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

Cruise — Mon, 12 Nov 2018 20:25:44 GMT

@novinosrin

so true, it doesn't work when min_diff ties up, just like below. When min_diff are the same across the different diagnosis codes that single patient took, I have to assign the weight to each diagnosis codes in terms of the strength of the evidence to prove of the disease. And the row associated wit the higher score for diagnosis code would be selected among the ties by min_diff.

data p.have;
input id code diff_range;
cards;
1 1 2
1 2 3
1 3 4
1 3 4
1 5 7
2 1 1
2 2 3
2 2 3
2 4 5
2 5 6
3 3 1
3 4 2
3 5 3
4 8 8
4 9 9
5 6 10
5 10 12
6 1 2
6 2 2
6 1 9
6 6 9
;

proc sql;
create table p.want_min1 as
select distinct * , min(diff_range) as min_diff from p.have
where id in
(select id from p.have where code in (1,2,6))
group by id
having diff_range=min_diff;
quit;

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

novinosrin — Mon, 12 Nov 2018 20:34:53 GMT

Ok,so do you have weighting variable in place?

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

mkeintz — Mon, 12 Nov 2018 20:43:39 GMT

Then make a minor modification to the proc sort solution. Assuming your weight variable is WGT:

proc sort data= have (where=(code in (1,2,6))) out=need;
  by Id diff_range descending wgt;
run;

data want;
  set need; 
  by id diff_range;
  if first.id;
run;

which will work perfectly unless you have an ID containing multiple records at the lowest diff_range having tied values at the highest wgt.

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

Cruise — Mon, 12 Nov 2018 20:47:06 GMT

@novinosrin

Yes, please see below:

data p.have;
input id code diff_range weight_evidence;
cards;
1 1 2 10
1 2 2 8
1 3 4 0
1 3 4 0
1 5 7 0
2 1 1 10
2 2 3 8
2 2 3 8
2 4 5 0
2 5 6 0
3 3 1 0
3 4 2 0
3 5 3 0
4 8 8 0
4 9 9 0
5 6 10 6
5 1 10 10
5 10 12 0
6 1 2 10
6 2 2 8
6 1 9 10
6 6 9 6
;

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

novinosrin — Mon, 12 Nov 2018 21:02:10 GMT


data have;
input id code diff_range weight_evidence;
cards;
1 1 2 10 
1 2 2 8 
1 3 4 0
1 3 4 0
1 5 7 0
2 1 1 10
2 2 3 8
2 2 3 8
2 4 5 0
2 5 6 0
3 3 1 0
3 4 2 0
3 5 3 0
4 8 8 0
4 9 9 0
5 6 10 6
5 1 10 10 
5 10 12 0
6 1 2 10 
6 2 2 8
6 1 9 10
6 6 9 6 
;
proc sql;
create table want_min1 as
select *
from 
(select distinct * , min(diff_range)as min_diff from have
where id in 
(select id from have where code in (1,2,6))
group by id)
group by id,min_diff
having weight_evidence=max(weight_evidence);
quit;

PS I will simplify the above if i can

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

Reeza — Mon, 12 Nov 2018 21:09:20 GMT

Does the minimum code have to be with diagnosis 1, 2, or 6? Or just a patient with one of those diagnosis? That will help novinosrin simplify his code.

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

Reeza — Mon, 12 Nov 2018 21:11:19 GMT

Cruise, can you post the expected output as well, how the weights need to be handled is unclear. Is it just part of the selection or are you reweighting?

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

Cruise — Mon, 12 Nov 2018 22:17:42 GMT

WEIGHT_EVIDENCE IS CREATED BASED ON THE CODE LIKE FOLLOWING:

if code=1 then weight_evidence=10; else
if code=2 then weight_evidence=8; else
if code=6 then weight_evidence=6;

HOWEVER, MIN_DIFF CAN RANGE 1 THRU 30.

In ideal world, where life goes our way, maximum weight of evidence would be corresponded with the minimum difference. However, there are discordant cases, which I thought of following as shown int he image. Please help me think of more discordant scenario if seem not considered here.

The Rationale for Selection and the Selected Cases should answer @Reeza and @novinosrin to understand how weight should be handled in the final output data.

Anything apparently incorrect? please let me know. I needed to think of what discordant cases might be. My actual data is huge and I have to resolve the issue on these hypothetical small dataset first. Hope that makes sense.

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

Reeza — Mon, 12 Nov 2018 22:20:40 GMT

Rather than try and think of all circumstances, what happens if you find all the cases that don't have issues, ie where min diff and max_weight and then see how many you have to deal with and consider them case by case?

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

novinosrin — Mon, 12 Nov 2018 22:30:29 GMT

I think the my sql will do that, test and let us know. Also, if you want to make your life easy, of course sort and datastep is by far simplest.

The below makes sql my style with fancy boolean although not much of big deal from the previous


data have;
input id code diff_range weight_evidence;
cards;
1 1 2 10 
1 2 2 8 
1 3 4 0
1 3 4 0
1 5 7 0
2 1 1 10
2 2 3 8
2 2 3 8
2 4 5 0
2 5 6 0
3 3 1 0
3 4 2 0
3 5 3 0
4 8 8 0
4 9 9 0
5 6 10 6
5 1 10 10 
5 10 12 0
6 1 2 10 
6 2 2 8
6 1 9 10
6 6 9 6 
;
proc sql;
create table want(drop=_:) as
select *
from 
(select *, max(code in (1,2,6)) as _c,min(diff_range) as _min from have group by id)
group by id,_min
having _c and max(weight_evidence)=weight_evidence and _min=diff_range;
quit;

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

Cruise — Mon, 12 Nov 2018 22:27:03 GMT

@Reeza, I'll test my real data with both approaches suggested by @novinosrin and @mkeintz . I'll let you know what happened.

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

Cruise — Mon, 12 Nov 2018 22:30:11 GMT

why max(code in (1,2,6))? codes will be assessed selected by max(weight_evidence) later?

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

novinosrin — Mon, 12 Nov 2018 22:33:05 GMT

max(code in (1,2,6))? results in a boolean 1s for id with 1,2,6 and 0's for id's that doesn't have 1, 2,6 . and i pick the 1s as you can imagine

I am not far away from mastering proc sql inside out. So i love playing all forms of it.. It's almost getting to the point, as the requirement narrates to my mind, the code is in my fingers 🙂

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

mkeintz — Tue, 13 Nov 2018 03:55:28 GMT

@Cruise wrote:

hI

WEIGHT_EVIDENCE IS CREATED BASED ON THE CODE LIKE FOLLOWING:

if code=1 then weight_evidence=10; else
if code=2 then weight_evidence=8; else
if code=6 then weight_evidence=6;

You've just told us that weight is a monotonic descending function of code. So a "by ... descending WGT" produces the same order as "by ... CODE". So I believe this will work:

proc sort data= have (where=(code in (1,2,6))) out=need;
  by Id diff_range code;
run;

data want;
  set need; 
  by id diff_range;
  if first.id;
run;

which will give you the smallest diff_ranges for each id. And in the case of tied smallest diff_range, it will choose the smallest code (i.e. the largest weight).

Re: How to select the distinct rows with when variables are not one-to-one with group by variable

Cruise — Tue, 13 Nov 2018 12:53:31 GMT

it sounds like you're a poet writing a sonnet in SQL language