Maybe start with your VCF. You could convert to BED, do a bedmap operation on a table of common SNPs, and then do a nucleotide lookup. With that information, you can then use awk and paste make your end product in the format you need.

1. Convert the VCF file to a seven-column BED file:

$ vcf2bed < variants.vcf | cut -f1-7 - > variants.bed7

2. Get the dbSNPs into a sorted BED file:

$ mysql -h genome-mysql.cse.ucsc.edu -A -u genome -D hg19 -e 'select chrom,chromStart,chromEnd,name from snp141' | awk '{if ($2==$3) $3++; print $0}'| sort-bed - > snps.bed

3. Map variants to SNPs with bedmap:

$ bedmap --indicator variants.bed7 snps.bed > mapped_variants.indicator

The file mapped_variants.indicator is a single-column text file with a 0 or 1, depending on if there is no overlap (0) or overlap (1) with a dbSNP annotation. Each row lines up with the original variant in variants.bed7.

For convenience, we can convert this to a YES/NO result:

$ awk '{if ($1=="0") print "NO"; else print "YES";}' mapped_variants.indicator > mapped_variants.yesno

4. Retrieve 3 nt centered on the variant position.

First, pad the variant coordinates by one base on both sides:

$ bedops --everything --range 1 variants.bed7 > padded_variants.bed7

Second, run the padded variants through bed2fasta or similar to get the nucleotides:

$ bed2fasta.py --genome-file hg19 padded_variants.bed7 > padded_variants.fasta
$ grep -v '^>' padded_variants.fasta > padded_variants.nt

5. You now have everything you need to make your final product:

• The file variants.bed7 has the ALT allele in the seventh column (along with the chromosome and position in the first and second columns).
• The file mapped_variants.yesno indicates if the variant overlaps a common SNP.
• The file padded_variants.nt has the three nucleotides centered on the variant position.

We can use awk to preprocess the BED file, then pipe this to a paste command, which glues this result to the nucleotides and yes/no call:

$ awk '{print $1":"$2","$7}' variants.bed7 | paste -d, - padded_variants.nt mapped_variants.yesno > answer.txt

The file answer.txt should be in your specified format.