topic Re: How to insert a value into a dataset when use another dataset to define value in SAS Programming

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

rengg0789 — Tue, 03 Mar 2020 05:20:28 GMT

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

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

andreas_lds — Tue, 03 Mar 2020 06:00:17 GMT

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;

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

ed_sas_member — Tue, 03 Mar 2020 11:03:06 GMT

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;

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

rengg0789 — Tue, 03 Mar 2020 19:35:15 GMT

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

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

rengg0789 — Tue, 03 Mar 2020 19:36:07 GMT

Thanks for your solution ed_sas_member!