unix - awk compare columns from two files, impute values of another column

Question

I want to compare the second columns from two files. If there is a match, print the corresponding value of the third column from the second file to the first file. If no match is found, fill with "NA"

File 1

1      rs1    AA    10
1      rs2    DD    20
1      rs3    EE    30
1      rs4    RR    40


File 2

1      rs1    Pascal
1      rs4    Albinoni


Desired output

1      rs1    AA    10    Pascal
1      rs2    DD    20    NA
1      rs3    EE    30    NA
1      rs4    RR    40    Albinoni

I have used this code, but it only output the matches:

awk 'NR==FNR { a[$1]=$2; next} $1 in a {print $0, a[$1]}' File2 File1

Thank you

Answer 1

awk 'FNR==NR{a[$2]=$3;next}{print $0,a[$2]?a[$2]:"NA"}' file2 file1

tested below:

> cat temp1
1      rs1    AA    10
1      rs2    DD    20
1      rs3    EE    30
1      rs4    RR    40
> cat temp2
1      rs1    Pascal
1      rs4    Albinoni
> awk 'FNR==NR{a[$2]=$3;next}{print $0,a[$2]?a[$2]:"NA"}' temp2 temp1
1      rs1    AA    10 Pascal
1      rs2    DD    20 NA
1      rs3    EE    30 NA
1      rs4    RR    40 Albinoni
>