AWK – Compare Records Value with Each Other

That worked for me:

awk -F, 'FNR==1{++f} \
  f==1 && $2==7 {a1[$1]++; a2[$2]=$3; o=$0} \
  f==2 && a1[$1] {o=o","$3","$4; a3[$3]=$4} \
  f==3 && a3[$1] && $2==a3[$1] && a2[$3] && $4<a2[$3] {print o}' \
file1 file2 file3

Explanation:

• The first line (FNR==1{++f}) increments the file index to later determine in which file we are 1-3.
• file1: if $2 equals 7
  • fill an array a1 with $1 as index and a2 with $2 as index and $3 as value
  • write down the o variable (output) with the first 3 fields
• file2: if $1 of file2 equals $1 of file1 (prevously written in a1)
  • append $3 and $4 to the output variable o.
  • fill an array a3 with $3 as index and $4 as value.
• file3: if:
  • $1 equals file2s $3 (index of a3)
  • $2 equals file2s $4 (value of a3)
  • $3 equals file1s $2 (index of a2)
  • $4 is lower than file1s $3 (value of a2)
• then:
  • print the value of o.

If the pattern cannot occur on consecutive lines you can simply run

sed '$!N;/.*\n.*PATTERN.*/P;D' infile

I've explained here how the N;P;D cycle works. The difference is that here the first line in the pattern space is printed only if the second one matches, otherwise it's deleted.

If the pattern can occur on consecutive lines the above solution will print a line that matches if it's followed by another line that matches.
To ignore consecutive matches add a second condition to print the first line in the pattern space only if it doesn't match:

sed '$!N;/.*\n.*PATTERN.*/{/.*PATTERN.*\n.*/!P;};D' infile

Another way, using the hold buffer.
If you want to ignore consecutive matches:

sed '/PATTERN/!{              # if line doesn't match PATTERN
h                             # copy pattern space content over the hold buffer
d                             # delete pattern space
}
//{                           # if line matches PATTERN
x                             # exchange pattern space with hold space
//d                           # if line matches PATTERN delete it
}' infile

or, in one line

sed '/PATTERN/!{h;d;};//{x;//d;}' infile

If you don't want to ignore consecutive matches:

sed '/PATTERN/!{              # if line doesn't match PATTERN
h                             # copy pattern space content over the hold buffer
d                             # delete pattern space
}
//x                           # if line matches PATTERN exchange buffers
' infile 

or, in one line

sed '/PATTERN/!{h;d;};//x' infile

Though keep in mind the solutions that use the hold buffer will print a leading empty line if the first line in your file matches.