I tried to get gene-names for a list of SNPs using the UCSC table browser using sql
However, I do not get the gene names either 3' or 5' of the SNP position.
I would like to get the nearest gene(s)(upstream or downstream 500kb) for a given SNP.
I would like to query the table GWAS Catalog and filter only for those associated with "cancer".
~> mysql -h genome-mysql.cse.ucsc.edu -A -u genome -D hg19 \
-e 'select * from gwasCatalog as G where (G.title like "%cancer%" or G.trait like "%cancer%" ) \G'
*************************** 1. row ***************************
bin: 756
chrom: chr1
chromStart: 22490723
chromEnd: 22490724
name: rs7521902
pubMedID: 20852632
author: Goode
pubDate: 2010-09-19
journal: Nat Genet
title: A genome-wide association study identifies susceptibility loci for ovarian cancer at 2q31 and 8q24
trait: Ovarian cancer
initSample: 1,768 European ancestry cases, 2,354 European ancestry controls
replSample: 8,709 European ancestry cases, 51,764 European ancestry controls
region: 1p36.12
genes: NR
riskAllele: rs7521902-?
riskAlFreq: NR
pValue: 5E-6
pValueDesc:
orOrBeta: 1.12
ci95: [1.07-1.18]
platform: Illumina [2,056,878] (imputed)
cnv: N
*************************** 2. row ***************************
bin: 1510
chrom: chr1
chromStart: 121280612
chromEnd: 121280613
name: rs11249433
pubMedID: 19330030
author: Thomas
pubDate: 2009-03-29
journal: Nat Genet
title: A multistage genome-wide association study in breast cancer identifies two new risk alleles at 1p11.2 and 14q24.1 (RAD51L1).
trait: Breast cancer
initSample: 1,145 cases, 1,142 controls
replSample: 8,625 cases, 9,657 controls
region: 1p11.2
genes: Intergenic
riskAllele: rs11249433-C
riskAlFreq: 0.39
pValue: 7E-10
pValueDesc:
orOrBeta: 1.16
ci95: [1.09-1.24]
platform: Illumina [528,173]
cnv: N
(...)
Pierre,
That works great! However, being a SQL newbie, can you write the part required to redirect the output to a text file. I tried using 'INTO OUTFILE' but did not work for me.
You can't: "The file is created on the server host, so you must have the FILE privilege to use this syntax."
Pierre, That works great! However, being a SQL newbie, can you write the part required to redirect the output to a text file. I tried using 'INTO OUTFILE' but did not work for me.
Using unix:
http://www.december.com/unix/tutor/redirect.html
How to add 'into dumpfile' to this command
Thanks Pierre. Much appreciated.