@Reeza, I'll test my real data with both approaches suggested by @novinosrin and @mkeintz . I'll let you know what happened.
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 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).
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
@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.
Yes a new thread is easier to follow. Thank you!
Works for me.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.