Hi fellow experts,
I have two datasets, called them dataset1 and dataset2. In dataset1, I have variables id, score, and grade. See below:
id score grade
1 80
2 76
3 55
...
Also, I have dataset2:
low high grade
86 100 A
71 85 B
60 70 C
0 59 F
Now I need to use score and dataset2 to determine the grade in the dataset 1. Anyone could help me write a code to do this? Thanks so much
Please post the expected result, also posting data in usable form increases the number of useful answers.
Idea to solve the problem: use dataset2 to create a format, use the put-function to translate the score to the appropriate grade.
data dataset1;
length id score 8 grade $ 1;
infile datalines missover;
input id score grade;
datalines;
1 80
2 76
3 55
;
run;
data dataset2;
length low high 8 grade $ 1;
input low high grade;
datalines;
86 100 A
71 85 B
60 70 C
0 59 F
;
run;
data GradeFormat;
set dataset2;
length FmtName $ 32 Type HLO $ 1;
retain FmtName "Score2Grade" Type "n" HLO "i";
rename
low = start
high = end
grade = label
;
run;
proc format cntlin=GradeFormat;
run;
data want;
set dataset1;
grade = put(score, Score2Grade.);
run;
Please post the expected result, also posting data in usable form increases the number of useful answers.
Idea to solve the problem: use dataset2 to create a format, use the put-function to translate the score to the appropriate grade.
data dataset1;
length id score 8 grade $ 1;
infile datalines missover;
input id score grade;
datalines;
1 80
2 76
3 55
;
run;
data dataset2;
length low high 8 grade $ 1;
input low high grade;
datalines;
86 100 A
71 85 B
60 70 C
0 59 F
;
run;
data GradeFormat;
set dataset2;
length FmtName $ 32 Type HLO $ 1;
retain FmtName "Score2Grade" Type "n" HLO "i";
rename
low = start
high = end
grade = label
;
run;
proc format cntlin=GradeFormat;
run;
data want;
set dataset1;
grade = put(score, Score2Grade.);
run;
Thanks so much andreas_lds. This is really helpful to me.
Hi @rengg0789
Another approach to the one proposed by @andreas_lds and using a PROC FORMAT could be:
data dataset1;
input id score;
datalines;
1 80
2 76
3 55
;
data dataset2;
input low high grade $;
datalines;
86 100 A
71 85 B
60 70 C
0 59 F
;
run;
proc sql;
create table want as
select a.id, a.score, b.grade
from dataset1 as a inner join dataset2 as b
on b.low <= a.score <= b.high;
quit;
Thanks for your solution ed_sas_member!
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.