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