How to insert a value into a dataset when use another dataset to define value

[rengg0789]

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

[andreas_lds · Accepted solution]

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;

[rengg0789]

Thanks so much andreas_lds. This is really helpful to me.

[ed_sas_member]

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;

[rengg0789]

Thanks for your solution ed_sas_member!