get scores based on age group and gender between 2 datasets

[noda6003]

I have 2 datasets -input1 and input2. Input1 contains scores based on id, age and sex and input2 contains grouping on age and scoret based age and gender. i need to scoret from input2 based on score, age and gender.

data INPUT1;
  infile datalines dsd truncover;
  input id:BEST12. score:BEST12. sex:$1. age:BEST12.;
datalines4;
1001,240,M,11
1001,239,M,11
1002,238,M,13
1002,237,M,13
1003,216,F,17
1003,215,F,17
1003,214,F,17
1003,217,F,17
;;;;

data INPUT2;
  infile datalines dsd truncover;
  input graw:BEST12. gtscore:BEST12. age:$5. sex:$1.;
datalines4;
240,91,11-14,M
239,90,11-14,M
238,90,11-14,M
237,90,11-14,M
236,89,11-14,M
235,89,11-14,M
234,88,11-14,M
233,88,15-18,M
232,88,15-18,M
231,87,15-18,M
230,87,15-18,M
229,87,15-18,M
228,86,15-18,M
227,86,15-18,M
226,85,15-18,M
225,85,15-18,M
224,85,11-14,F
223,84,11-14,F
222,84,11-14,F
221,84,11-14,F
220,83,11-14,F
219,83,11-14,F
218,82,11-14,F
217,82,15-18,F
216,82,15-18,F
215,81,15-18,F
214,81,15-18,F
;;;;

i want the both to get the below result

data RESULT;
  infile datalines dsd truncover;
  input id:BEST12. score:BEST12. sex:$1. age:BEST12. score_t:BEST12.;
datalines4;
1001,240,M,11,91
1001,239,M,11,90
1002,238,M,13,90
1002,237,M,13,90
1003,216,F,17,82
1003,215,F,17,81
1003,214,F,17,81
1003,217,F,17,82
;;;;

Any help on how to do the above

[Kurt_Bremser]

Create a format to convert age to age group, then use a hash object for your multiple keys:

proc format;
value agegroup
  11-14 = '11-14'
  15-18 = '15-18'
;
run;

data want;
set input1;
if _n_ = 1
then do;
  length
    agegroup $5
    score_t 8
  ;
  declare hash ag (dataset:"input2 (rename=(graw=score age=agegroup gtscore=score_t))");
  ag.definekey("agegroup","sex","score");
  ag.definedata("score_t");
  ag.definedone();
end;
agegroup = put(age,agegroup.);
if ag.find() ne 0 then score_t = .;
run;

---

@noda6003 wrote:

I have 2 datasets -input1 and input2. Input1 contains scores based on id, age and sex and input2 contains grouping on age and scoret based age and gender. i need to scoret from input2 based on score, age and gender.

data INPUT1;
  infile datalines dsd truncover;
  input id:BEST12. score:BEST12. sex:$1. age:BEST12.;
datalines4;
1001,240,M,11
1001,239,M,11
1002,238,M,13
1002,237,M,13
1003,216,F,17
1003,215,F,17
1003,214,F,17
1003,217,F,17
;;;;

data INPUT2;
  infile datalines dsd truncover;
  input graw:BEST12. gtscore:BEST12. age:$5. sex:$1.;
datalines4;
240,91,11-14,M
239,90,11-14,M
238,90,11-14,M
237,90,11-14,M
236,89,11-14,M
235,89,11-14,M
234,88,11-14,M
233,88,15-18,M
232,88,15-18,M
231,87,15-18,M
230,87,15-18,M
229,87,15-18,M
228,86,15-18,M
227,86,15-18,M
226,85,15-18,M
225,85,15-18,M
224,85,11-14,F
223,84,11-14,F
222,84,11-14,F
221,84,11-14,F
220,83,11-14,F
219,83,11-14,F
218,82,11-14,F
217,82,15-18,F
216,82,15-18,F
215,81,15-18,F
214,81,15-18,F
;;;;

i want the both to get the below result

data RESULT;
  infile datalines dsd truncover;
  input id:BEST12. score:BEST12. sex:$1. age:BEST12. score_t:BEST12.;
datalines4;
1001,240,M,11,91
1001,239,M,11,90
1002,238,M,13,90
1002,237,M,13,90
1003,216,F,17,82
1003,215,F,17,81
1003,214,F,17,81
1003,217,F,17,82
;;;;

Any help on how to do the above

---

[yabwon]

Hi,

Maybe a simple SQL left join?

data INPUT2V / view = INPUT2V;
  set INPUT2;
  start = input(scan(age,1,"-"),best.);
  end   = input(scan(age,2,"-"),best.);
run;

ods html;
proc sql;
create table WANT as
select a.*, b.gtscore as score_t
from
  INPUT1 as a
  left join
  INPUT2V as b
  on 
  a.score = b.graw
  and a.sex = b.sex
  and a.age between b.start and b.end
  order by a.id, a.score desc, a.sex, a.age
  ;
quit;
proc print;
run;

All the best

Bart