Conditionally insert a row for unique id

[PDevi]

Hi there, I need help with conditionally inserting a missing data row for each unique id. I have the following sample date:

Id	Test_date	Test_id	Test_component	Test_score	Score_letter
1	20-09-2011	XYZ	English	2.5
1	20-09-2011	XYZ	Math	4.0
1	20-09-2011	XYZ	Science	6.0
1	20-09-2011		Recommendation		DIP
2	30-05-2015	XYZ	English	4.5
2	30-05-2015	XYZ	Math	7
2	30-05-2015	XYZ	Science	4
2	30-05-2015		Recommendation		DEG
3	18-12-2013	XYZ	English	4.5
3	18-12-2013	XYZ	Math	4
3	18-12-2013	XYZ	Science	8
4	12-12-2012	XYZ	English	4.5
4	12-12-2012	XYZ	Math	4.5
4	12-12-2012	XYZ	Science	8
4	12-12-2012		Recommendation		MAS

The recommendation is based on the following grid:

	Test_component/Scores
Score_letter	English	Math	Science
DIP	<4	<4	<7
DEG	4	4	7
MAS	4.5	4.5	8

A student would need the above exact scores for each of the components for that score_letter to get that particular recommendation. So, for example, to get a recommendation of 'MAS', s/he would need a score of 4.5 each in English and Math and a 8 in Science. In case s/he gets a 3 in English, 4 in Math and 8 in Science, the recommendation would be based on the lowest, so would be 'DIP'. 

I am missing the recommendation for some students as seen for id #3 (it will be 'DEG' here) above and need to insert the row based on the conditions for the score_letter. Can somebody please help me figure this out? I am at a complete loss here. Thanks in advance.

[Patrick]

Below should work.

data want;
  set have;
  /* also sorted by test_score to ensure Recommendation is the last row within an id */
  by id test_score;
  output;

  array _test_score {3} 8 _temporary_;
  if not last.id then
    do;
      select(upcase(test_component));
        when('ENGLISH') _test_score[1]=test_score;
        when('MATH')    _test_score[2]=test_score;
        when('SCIENCE') _test_score[3]=test_score;
        otherwise;
      end;
    end;

  else
  if last.id then
    do;
      if upcase(test_component) ne 'RECOMMENDATION' then
        do;
          test_component='Recommendation';
          if _test_score[1]<4 and _test_score[2]<4 and _test_score[3]<7 then score_letter='Dip';
/*          else if....*/
          call missing(test_id, test_score);
          output;
        end;
      call missing(of _test_score[*]);
    end;
run;

[Satish_Parida]

/*Please let us know if it worked for you.*/

data std_data;
infile cards dlm='|' dsd missover;
input Id:best12. Test_date:ddmmyy10. Test_id:$4. Test_component:$20. Test_score:best12.;
format Test_date:ddmmyy10.;
cards;
1|20-09-2011|XYZ|English|2.5
1|20-09-2011|XYZ|Math|4
1|20-09-2011|XYZ|Science|6
2|30-05-2015|XYZ|English|4.5
2|30-05-2015|XYZ|Math|7
2|30-05-2015|XYZ|Science|4
3|18-12-2013|XYZ|English|4.5
3|18-12-2013|XYZ|Math|4
3|18-12-2013|XYZ|Science|8
4|12-12-2012|XYZ|English|4.5
4|12-12-2012|XYZ|Math|4.5
4|12-12-2012|XYZ|Science|8
;
run;

proc transpose data=std_data out=std_data_trans;
by id Test_date Test_id;
var Test_score;
id Test_component;
run;

data condn;
infile cards dlm='|' dsd missover;
input Score_letter:$3. English:$5. Math:$5. Science:$5. ;
cards;
DIP|<4|<4|<7
DEG|4|4|7
MAS|4.5|4.5|8
;
run;

data want;
set std_data_trans;
if English lt 4 and Math lt 4 and Science lt 7 then Score_letter='DIP';
else if English le 4.5 and Math le 4.5 and Science lt 8 then Score_letter='DEG';
else if English ge 4.5 and Math ge 4.5 and Science ge 8 then Score_letter='MAS';
run;

proc transpose data=want out=want_trans;
by id Test_date Test_id;
var English Math Science Score_letter;
run;

data want_trans(drop=Test_Score rename=(_name_=Test_component Test_Score1=Test_Score));
set want_trans;
if _name_='Score_letter' then do;
	Test_id='';
	Test_component='Recommendation';
	Score_letter=Test_Score;
	Test_Score='';
end;
Test_Score1=input(Test_Score,best12.);
run;