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

[Cruise]

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

[Cruise]

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

[novinosrin]

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 🙂

[Cruise]

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

[mkeintz]

---

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

[Cruise]

The ICD-9 code also includes alpha numeric and I needed to assign ordinal numbers to these codes by using if-then logic. I'm wondering then proc sort by weigh_evidence would do instead code itself. Sorry the value I chose for 'code' was  a bad example in the original post. 
153
154
159
197
199
209
211
230
235
239
V10
V76
C18
C20

[Cruise]

@Reeza, @novinosrin, @mkeintz, @r_behata

This post had evolved from:

- picking the rows associated with the minimum value of one specific variable (min_diff) where other variable 'code" takes values of  (1,2,6). 

to:

the need to investigate: 

- how much is the compromise in the 'min_diff' if you select the cases only based on the strength of evidence (relevance of diagnostic codes to the disease of interest) regardless of 'min_diff'. 

Shall I create a new post to investigate the level of compromise in 'min_diff' if selection criteria is based on the strength of evidence only? I now have a clue what's happening in my actual data thus this new question emerged. I learned from your alternative approaches and questions asked during this post. 

[novinosrin]

Yes a new thread is easier to follow. Thank you!

[mkeintz]

Works for me.