@Reeza Thank you for updating the post. @Patrick Thanks Patrick, For taking time to look at it. I am sorry if i was not clear and complete with my requirements, As Reeza mentioned I should have put all information ( I apologize as I am new to posting questions, I will learn) 🙂 I have added few more rows to input dataset, and also attached the desired output. Complete Requirements ( added bold text to it 😞 I have to check each unique "score" for each testcode ( from the excel) in the dataset columns Score1 - Score120 , If there is a hit, I need to take corresponding "Final score" column ( from 120 columns in dataset) and lookup that "Final score" against the excel for the same testcode and score , and find out what range of upperlimit and lowerlimit it falls in, ( If it doesn't fall in any of the range in excel sheet for that test and score, we create a flag called error_Final Score(_i) = 1, also we create a counter effor_flag, ) , If I have the hit, I need to take the "Proficiency" value from the excel and check that value in dataset "Prof_Level" column, we check the proficiency from excel in Prof_Level only when that "score" is present in the Prof_Level , else we move to next score from excel, if the "score" is there in Prof_Level then we check Proficiency value from excel next to the matched score in Prof_level and if the value doesn't match we generate error on Prof_Level ( Error_Prof_Level =1 ) and increment effor_flag+1, if we do not have the "score" from excel that we are checking in this iteration not there in Prof_Level at all , we do not do anything, just move to the next "score" from excel for that test and repeat. If we have another mismatch like this for that record , we generate error on Prof_Level1 and increment error_flag+1; Note - Because there will be max two proficiency in "Prof_level" column in DS , I created one more column Prof_level1 to capture both errors if two matches for a testcode fails. Example: From excel sheet, we take Score = 1 for testcode 41 and try to find it in the dataset column Score1-Score120 , We have "Score1" in dataset =1, so thats a hit, Now we take corresponding Final Score from dataset, in this case "Final_score1" = 74, Now we take 74 and check in excel sheet against testcode 41 and score=1 and see what range it falls in ( If it doesn't fall in any of the range in excel sheet for that test and score, we create a flag called error_Final Score(_i) = 1, also we create a counter say effor_flag, ) , now in our data It falls in row 1 in excel sheet, Now We take value "Low" from there and check "PROF_Level" in dataset if it has the "Low" next to "1". and we have Low next to "1" so we are good, else If we have "score" from excel sheet in "PROF_Level" in dataset but the "proficiency from excel doesn't match with the string next to the score then we generate error, and increment effor_flag+1, if we do not have the "score" from excel that we are checking in this iteration not there in Prof_Level at all , we do not do anything, just move to the next "score" from excel and repeat. data ds; infile datalines truncover dlm='|'; input Testcode score1 score2 score120 Final_score1 Final_score2 Final_score120 Prof_level ~ $20. Prof_level1 ~ $20. ; datalines; 41|1|4.5|23.4|74|230|555|1 Low 2.2 Medium|1 Low 2.2 Medium 41|.|5.6|.|450|130|250|2.2 Medium 2 High|2.2 Medium 2 High 51|6.6|2.1|23.4|450|130|555|2.1 Medium 1 Low|2.1 Medium 1 Low 51|6.6|2.9|1|450|130|175|1 Medium 1 Low|2.1 Medium 1 Low 51|1|2|2.1|175|130|175|1 Medium 2 Low|1 Medium 2 Low 51|1|2|2.1|175|130|175|1 High 2.1 Low|1 High 2.1 Low ; data Excel; infile datalines truncover dlm='|'; input Testcode Proficiency :$10. MatchScore Lowerlimit Upperlimit $20.; datalines; 41|Low|1|50|100 41|Medium|1|101|150 41|High|1|151|200 41|Low|2|75|125 41|Medium|2|126|175 41|High|2|176|200 51|Low|1|50|100 51|Medium|1|101|150 51|High|1|151|200 51|Low|2.1|75|125 51|Medium|2.1|126|175 51|High|2.1|176|200 ; Example below should not flag an error as there is no match of the score ( 2.1 ) in the "Prof_Level" column (1 & 2.2 ). data ds; infile datalines truncover dlm='|'; input Testcode score1 score2 score120 Final_score1 Final_score2 Final_score120 Prof_level ~ $20. Prof_level ~ $20. ; datalines; 51|6.6|2.1|23.4|450|130|555|1 Low 2.2 Medium|1 Low 2.2 Medium ; data Excel; infile datalines truncover dlm='|'; input Testcode Proficiency :$10. MatchScore Lowerlimit Upperlimit $20.; datalines; 51|Low|2.1|75|125 51|Medium|2.1|126|175 51|High|2.1|176|200 ; Desired output: It would be great if you can please help on this. Appreciate all your help, have a nice day. Thanks Manoj
... View more