Hello Team,
I need help in how to check the reported lab results values are within the Min & max values data as in below table :
i want to check Value recorded is within the min range & max range provided.
Thank you!
Min Range | Max Range | Unit | Value |
Nil | |||
Nil | |||
Nil | |||
Nil | |||
Nil | |||
1 | 1.035 | 1.007 | |
Nil | |||
4.6 | 9.0 | 6.0 | |
Nil | |||
Nil | |||
<5.0 | umol/L | 4.0 | |
<25 | umol/L | 11 | |
umol/L | 7 | ||
<201 | U/L | 145 | |
35 | 50 | g/L | 43 |
30 | 120 | U/L | 50 |
<51 | U/L | 31 | |
9 | 19 | mmol/L | 11 |
<41 | U/L | 18 | |
22 | 32 | mmol/L | 28 |
2.10 | 2.60 | mmol/L | 2.42 |
2.10 | 2.60 | mmol/L | 2.46 |
95 | 110 | mmol/L | 104 |
<5.6 | mmol/L | 4.9 | |
60 | 110 | umol/L | 90 |
>59 | mL/min/1.73m2 | > 90 | |
<51 | U/L | 36 | |
23 | 39 | g/L | 29 |
3.0 | 6.9 | mmol/L | 4.2 |
1.07 | 1.27 | mmol/L | 1.25 |
50 | 280 | U/L | 199 |
0.75 | 1.50 | mmol/L | 1.05 |
3.5 | 5.2 | mmol/L | 3.8 |
60 | 82 | g/L | 72 |
135 | 145 | mmol/L | 139 |
3.5 | 8.0 | mmol/L | 7.1 |
0.18 | 0.47 | mmol/L | 0.36 |
35 | 50 | g/L | 43 |
22 | 32 | mmol/L | 28 |
2.10 | 2.60 | mmol/L | 2.42 |
2.10 | 2.60 | mmol/L | 2.46 |
95 | 110 | mmol/L | 104 |
3.5 | 5.2 | mmol/L | 3.8 |
60 | 82 | g/L | 72 |
135 | 145 | mmol/L | 139 |
23.0 | 35.0 | s | 25.9 |
0.9 | 1.2 | 1.1 | |
9.5 | 13.0 | s | 11.2 |
<0.3 | x10{S9}/L | < 0.1 | |
% | 1 | ||
<0.7 | x10{S9}/L | < 0.1 | |
% | 2 | ||
0.40 | 0.55 | 0.39 | |
125 | 175 | g/L | 132 |
1.0 | 4.0 | x10{S9}/L | 1.3 |
% | 32 | ||
27.0 | 34.0 | pg | 29.6 |
80 | 99 | fL | 88 |
310 | 360 | g/L | 336 |
<1.1 | x10{S9}/L | 0.3 | |
% | 8 | ||
fL | 10.6 | ||
2.0 | 8.0 | x10{S9}/L | 2.3 |
% | 57 | ||
150 | 450 | x10{S9}/L | 198 |
4.50 | 6.50 | x10{S12}/L | 4.46 |
11.0 | 15.0 | % | 13.0 |
0.20 | 2.00 | % | 1.64 |
10 | 100 | x10{S9}/L | 73 |
4.0 | 11.0 | x10{S9}/L | 4.1 |
Not Detected | |||
Not Detected | |||
Not Detected | |||
0 | |||
0 | |||
0 | |||
Non Reactive | |||
Negative | |||
S/CO | < 0.02 | ||
IU/L | < 0.10 | ||
Not Detected | |||
<20.01 | mIU/ml | 0.12 | |
<5.0 | umol/L | 3.6 | |
<25 | umol/L | 10 | |
umol/L | 6 | ||
<201 | U/L | 152 | |
35 | 50 | g/L | 47 |
30 | 120 | U/L | 55 |
<51 | U/L | 56 | |
9 | 19 | mmol/L | 11 |
<41 | U/L | 29 | |
22 | 32 | mmol/L | 27 |
2.10 | 2.60 | mmol/L | 2.44 |
2.10 | 2.60 | mmol/L | 2.54 |
95 | 110 | mmol/L | 104 |
<5.6 | mmol/L | 5.4 | |
60 | 110 | umol/L | 89 |
>59 | mL/min/1.73m2 | > 90 | |
<51 | U/L | 44 | |
23 | 39 | g/L | 30 |
3.0 | 6.9 | mmol/L | 4.5 |
1.07 | 1.27 | mmol/L | 1.25 |
50 | 280 | U/L | 216 |
0.75 | 1.50 | mmol/L | 1.10 |
3.5 | 5.2 | mmol/L | 4.4 |
60 | 82 | g/L | 77 |
135 | 145 | mmol/L | 138 |
3.5 | 8.0 | mmol/L | 7.6 |
0.18 | 0.47 | mmol/L | 0.41 |
35 | 50 | g/L | 47 |
22 | 32 | mmol/L | 27 |
2.10 | 2.60 | mmol/L | 2.44 |
2.10 | 2.60 | mmol/L | 2.54 |
95 | 110 | mmol/L | 104 |
3.5 | 5.2 | mmol/L | 4.4 |
60 | 82 | g/L | 77 |
135 | 145 | mmol/L | 138 |
23.0 | 35.0 | s | 27.1 |
0.9 | 1.2 | 1.0 | |
9.5 | 13.0 | s | 10.9 |
<0.3 | x10{S9}/L | < 0.1 | |
% | 1 | ||
<0.7 | x10{S9}/L | 0.1 | |
% | 3 |
Before you can write code you need to have a RULE. What is the RULE? Remember that MIN, MAX and VALUE all appear to be character variables, so the rule has to include how to convert them into numbers so that they can be compare.
Also what the heck do you do with the observations that have nothing for either MIN or MAX?
Compare exactly what?
When you have that many different values in a single column called "max range" then it is pretty obvious that these values are not all compared to the same measurements because you have stuff like <5 and <25 with the same apparent unit of measure. So if "5" is a max then "25: would be way past maximum.
And <5 would mean you have to provide the actual value you want for YOUR process.
Personally I find this to be a next a useless question. You don't provide how any of this is to be used, no "reported lab results" to compare, are apparently using many different types of measurements.
I have worked with instrument data for comparisons. So a specific instrument, or group of instruments, would be compared to a given numeric range. You "data" would have to include the instrument to be even partially useful. If the recorded measurements are numeric then you must provide a numeric value. <5 is not numeric. So somewhere you also need a place that says the value is compared to this number and either report a problem when the value exceeds/ is below some range or indicate it is acceptable. You haven't provided any actual use case.
Note that is this were structured correctly, such as indicating the type of instrument or measurement then you would have to be applying the min and max only to those types of measures. But there is no such indication.
@Sri_devi wrote:
For data values with blank Max and Min can be ignored.
Rule in converting character to numeric. DO you mean to write a code to convert those. Can you please provide details on how to do it please.
thanks!
No. You need to explain how you want the code to work. Then we can help you with the code. Since some of the values of MAX are not numbers you need to explain what number you want them converted into. And what the meaning of the extra characters that make them not actual numbers means. For example what is the difference in meaning between MAX='<5' and MAX='5' ? What does it mean in MAX is populated and MIN is not? What do you want to do with those non numeric values of VALUE?
You didn't post the output you want yet !
Here could you a start.
data have;
infile datalines expandtabs truncover;
input (Min Max Unit Value) (:$40.);
new_min=input(compress(min,'.','kd'),best32.);
new_max=input(compress(max,'.','kd'),best32.);
new_value=input(compress(value,'.','kd'),best32.);
if missing(new_min) and not missing(new_max) then new_min=.;
if not missing(new_min) and missing(new_max) then new_max=constant('bigint');
if new_min<new_value<new_max then flag=1;
drop new_min new_max new_value;
datalines;
. . . Nil
. . . Nil
1 1.035 . 1.007
. <5.0 umol/L 4.0
. <25 umol/L 11
>59 . mL/min/1.73m2 >90
. <0.3 x10{S9}/L <0.1
;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.