Hallo @Barney1998,
You could retrieve the relevant bounds from a hash object created from your second dataset. This requires that the lookup can be based on an exact match of the age value. In the example below this is achieved by rounding age values to one decimal and having a complete lookup table, i.e., with no gaps in the sequence 12.0, 12.1, ..., 17.9, 18.0. (You might need to fill gaps in your real data.) Height values are rounded to two decimals to make sure that borderline values are classified correctly.
/* Create sample data for demonstration */
data have;
call streaminit(2377);
do id=1 to 100;
age=rand('uniform',12,18);
height=round(0.067*age+rand('normal',0.6,0.07),0.01);
output;
end;
run;
data ranges;
do _n_=120 to 180;
age=_n_/10;
h1=round(0.067*age+0.46,0.01);
h2=round(h1+0.07,.01);
h3=round(h2+0.14,.01);
output;
end;
run;
/* Classify heights depending on age */
proc format;
value hcfmt
1='too short'
2='short'
3='normal'
4='tall';
run;
data want(drop=h1-h3);
set have;
if _n_=1 then do;
dcl hash h(dataset:'ranges');
h.definekey('age');
h.definedata('h1','h2','h3');
h.definedone();
if 0 then set ranges;
end;
if n(age, height)=2 then
if h.find(key:round(age,0.1))=0 then c=1+(height>=h1)+(height>=h2)+(height>=h3);
format c hcfmt.;
run;
These are the first four observations in dataset WANT:
Obs id age height c
1 1 13.8719 1.39 short
2 2 14.4157 1.53 normal
3 3 15.9520 1.74 tall
4 4 16.8918 1.58 too short
... and the corresponding observations from dataset RANGES:
Obs age h1 h2 h3
20 13.9 1.39 1.46 1.60
25 14.4 1.42 1.49 1.63
41 16.0 1.53 1.60 1.74
50 16.9 1.59 1.66 1.80
... View more