API tools faq
paste
Advertisement
Guest User

Import VAERS data to CSV

a guest
Jul 15th, 2021
125
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 8.31 KB | None | 0 0
raw download clone embed print report
  1. /**
  2.  * Data sourced from https://vaers.hhs.gov/data/datasets.html
  3.  * 1. Download the zip file of "All Years Data"
  4.  * 2. Unzip the zip file
  5.  * 3. In that directory, run sqlite3 VAERS_ALL.db
  6.  * 4. Copy and paste the below to populate that database
  7.  */
  8. .header ON
  9. .mode csv
  10.  
  11. CREATE TABLE vaers_data(
  12.       "vaers_id" INTEGER PRIMARY KEY NOT NULL,
  13.       "recvdate" DATE NOT NULL,
  14.       "state" text NOT NULL,
  15.       "age_yrs" INTEGER,
  16.       "cage_yr" INTEGER,
  17.       "cage_mo" FLOAT,
  18.       "sex" text,
  19.       "rpt_date" DATE,
  20.       "symptom_text" text,
  21.       "died" BOOLEAN,
  22.       "datedied" DATE,
  23.       "l_threat" BOOLEAN,
  24.       "er_visit" BOOLEAN,
  25.       "hospital" BOOLEAN,
  26.       "hospdays" INTEGER,
  27.       "x_stay" BOOLEAN,
  28.       "disable" BOOLEAN,
  29.       "recovd" text,
  30.       "vax_date" DATE,
  31.       "onset_date" DATE,
  32.       "numdays" INTEGER,
  33.       "lab_data" text,
  34.       "v_adminby" text,
  35.       "v_fundby" text,
  36.       "other_meds" text,
  37.       "cur_ill" text,
  38.       "history" text,
  39.       "prior_vax" text,
  40.       "splttype" text,
  41.       "form_vers" text,
  42.       "todays_date" text,
  43.       "birth_defect" text,
  44.       "ofc_visit" text,
  45.       "er_ed_visit" text,
  46.       "allergies" text
  47. );
  48. CREATE TABLE vaers_symptoms(
  49.       "vaers_id" INTEGER,
  50.       "symptom1" text,
  51.       "symptomversion1" text,
  52.       "symptom2" text,
  53.       "symptomversion2" text,
  54.       "symptom3" text,
  55.       "symptomversion3" text,
  56.       "symptom4" text,
  57.       "symptomversion4" text,
  58.       "symptom5" text,
  59.       "symptomversion5" text
  60. );
  61. /* Because the schema for symptoms is awful */
  62. CREATE TABLE vaers_symptom(
  63.       "vaers_id" INTEGER,
  64.       "symptom" text,
  65.       "symptomversion" FLOAT
  66. );
  67. CREATE TABLE vaers_vax(
  68.       "vaers_id" INTEGER,
  69.       "vax_type" text,
  70.       "vax_manu" text,
  71.       "vax_lot" text,
  72.       "vax_dose_series" text,
  73.       "vax_route" text,
  74.       "vax_site" text,
  75.       "vax_name" text
  76. );
  77.  
  78.  
  79. .import ./1990VAERSDATA.csv vaers_data
  80. .import ./1990VAERSSYMPTOMS.csv vaers_symptoms
  81. .import ./1990VAERSVAX.csv vaers_vax
  82. .import ./1991VAERSDATA.csv vaers_data
  83. .import ./1991VAERSSYMPTOMS.csv vaers_symptoms
  84. .import ./1991VAERSVAX.csv vaers_vax
  85. .import ./1992VAERSDATA.csv vaers_data
  86. .import ./1992VAERSSYMPTOMS.csv vaers_symptoms
  87. .import ./1992VAERSVAX.csv vaers_vax
  88. .import ./1993VAERSDATA.csv vaers_data
  89. .import ./1993VAERSSYMPTOMS.csv vaers_symptoms
  90. .import ./1993VAERSVAX.csv vaers_vax
  91. .import ./1994VAERSDATA.csv vaers_data
  92. .import ./1994VAERSSYMPTOMS.csv vaers_symptoms
  93. .import ./1994VAERSVAX.csv vaers_vax
  94. .import ./1995VAERSDATA.csv vaers_data
  95. .import ./1995VAERSSYMPTOMS.csv vaers_symptoms
  96. .import ./1995VAERSVAX.csv vaers_vax
  97. .import ./1996VAERSDATA.csv vaers_data
  98. .import ./1996VAERSSYMPTOMS.csv vaers_symptoms
  99. .import ./1996VAERSVAX.csv vaers_vax
  100. .import ./1997VAERSDATA.csv vaers_data
  101. .import ./1997VAERSSYMPTOMS.csv vaers_symptoms
  102. .import ./1997VAERSVAX.csv vaers_vax
  103. .import ./1998VAERSDATA.csv vaers_data
  104. .import ./1998VAERSSYMPTOMS.csv vaers_symptoms
  105. .import ./1998VAERSVAX.csv vaers_vax
  106. .import ./1999VAERSDATA.csv vaers_data
  107. .import ./1999VAERSSYMPTOMS.csv vaers_symptoms
  108. .import ./1999VAERSVAX.csv vaers_vax
  109. .import ./2000VAERSDATA.csv vaers_data
  110. .import ./2000VAERSSYMPTOMS.csv vaers_symptoms
  111. .import ./2000VAERSVAX.csv vaers_vax
  112. .import ./2001VAERSDATA.csv vaers_data
  113. .import ./2001VAERSSYMPTOMS.csv vaers_symptoms
  114. .import ./2001VAERSVAX.csv vaers_vax
  115. .import ./2002VAERSDATA.csv vaers_data
  116. .import ./2002VAERSSYMPTOMS.csv vaers_symptoms
  117. .import ./2002VAERSVAX.csv vaers_vax
  118. .import ./2003VAERSDATA.csv vaers_data
  119. .import ./2003VAERSSYMPTOMS.csv vaers_symptoms
  120. .import ./2003VAERSVAX.csv vaers_vax
  121. .import ./2004VAERSDATA.csv vaers_data
  122. .import ./2004VAERSSYMPTOMS.csv vaers_symptoms
  123. .import ./2004VAERSVAX.csv vaers_vax
  124. .import ./2005VAERSDATA.csv vaers_data
  125. .import ./2005VAERSSYMPTOMS.csv vaers_symptoms
  126. .import ./2005VAERSVAX.csv vaers_vax
  127. .import ./2006VAERSDATA.csv vaers_data
  128. .import ./2006VAERSSYMPTOMS.csv vaers_symptoms
  129. .import ./2006VAERSVAX.csv vaers_vax
  130. .import ./2007VAERSDATA.csv vaers_data
  131. .import ./2007VAERSSYMPTOMS.csv vaers_symptoms
  132. .import ./2007VAERSVAX.csv vaers_vax
  133. .import ./2008VAERSDATA.csv vaers_data
  134. .import ./2008VAERSSYMPTOMS.csv vaers_symptoms
  135. .import ./2008VAERSVAX.csv vaers_vax
  136. .import ./2009VAERSDATA.csv vaers_data
  137. .import ./2009VAERSSYMPTOMS.csv vaers_symptoms
  138. .import ./2009VAERSVAX.csv vaers_vax
  139. .import ./2010VAERSDATA.csv vaers_data
  140. .import ./2010VAERSSYMPTOMS.csv vaers_symptoms
  141. .import ./2010VAERSVAX.csv vaers_vax
  142. .import ./2011VAERSDATA.csv vaers_data
  143. .import ./2011VAERSSYMPTOMS.csv vaers_symptoms
  144. .import ./2011VAERSVAX.csv vaers_vax
  145. .import ./2012VAERSDATA.csv vaers_data
  146. .import ./2012VAERSSYMPTOMS.csv vaers_symptoms
  147. .import ./2012VAERSVAX.csv vaers_vax
  148. .import ./2013VAERSDATA.csv vaers_data
  149. .import ./2013VAERSSYMPTOMS.csv vaers_symptoms
  150. .import ./2013VAERSVAX.csv vaers_vax
  151. .import ./2014VAERSDATA.csv vaers_data
  152. .import ./2014VAERSSYMPTOMS.csv vaers_symptoms
  153. .import ./2014VAERSVAX.csv vaers_vax
  154. .import ./2015VAERSDATA.csv vaers_data
  155. .import ./2015VAERSSYMPTOMS.csv vaers_symptoms
  156. .import ./2015VAERSVAX.csv vaers_vax
  157. .import ./2016VAERSDATA.csv vaers_data
  158. .import ./2016VAERSSYMPTOMS.csv vaers_symptoms
  159. .import ./2016VAERSVAX.csv vaers_vax
  160. .import ./2017VAERSDATA.csv vaers_data
  161. .import ./2017VAERSSYMPTOMS.csv vaers_symptoms
  162. .import ./2017VAERSVAX.csv vaers_vax
  163. .import ./2018VAERSDATA.csv vaers_data
  164. .import ./2018VAERSSYMPTOMS.csv vaers_symptoms
  165. .import ./2018VAERSVAX.csv vaers_vax
  166. .import ./2019VAERSDATA.csv vaers_data
  167. .import ./2019VAERSSYMPTOMS.csv vaers_symptoms
  168. .import ./2019VAERSVAX.csv vaers_vax
  169. .import ./2020VAERSDATA.csv vaers_data
  170. .import ./2020VAERSSYMPTOMS.csv vaers_symptoms
  171. .import ./2020VAERSVAX.csv vaers_vax
  172. .import ./2021VAERSDATA.csv vaers_data
  173. .import ./2021VAERSSYMPTOMS.csv vaers_symptoms
  174. .import ./2021VAERSVAX.csv vaers_vax
  175.  
  176. DELETE FROM vaers_vax WHERE vaers_id='vaers_id';
  177. DELETE FROM vaers_symptoms WHERE vaers_id='vaers_id';
  178. DELETE FROM vaers_data WHERE vaers_id='vaers_id';
  179.  
  180. CREATE INDEX vaers_data_vaers_id ON vaers_data (vaers_id);
  181. CREATE INDEX vaers_symptom_vaers_id ON vaers_symptom (vaers_id);
  182. CREATE INDEX vaers_vax_vaers_id ON vaers_vax (vaers_id);
  183. CREATE INDEX vaers_vax_vax_type ON vaers_vax (vax_type);
  184.  
  185. UPDATE vaers_data
  186. SET died = CASE WHEN died = 'Y' THEN 1 ELSE 0 END,
  187.     l_threat = CASE WHEN l_threat = 'Y' THEN 1 ELSE 0 END,
  188.     er_visit = CASE WHEN er_visit = 'Y' THEN 1 ELSE 0 END,
  189.     hospital = CASE WHEN hospital = 'Y' THEN 1 ELSE 0 END,
  190.     x_stay = CASE WHEN x_stay = 'Y' THEN 1 ELSE 0 END,
  191.     disable = CASE WHEN disable = 'Y' THEN 1 ELSE 0 END;
  192.  
  193. INSERT INTO vaers_symptom (vaers_id, symptom, symptomversion)
  194. SELECT vaers_id, symptom1, symptomversion1 FROM vaers_symptoms WHERE symptomversion1 != '';
  195. INSERT INTO vaers_symptom (vaers_id, symptom, symptomversion)
  196. SELECT vaers_id, symptom2, symptomversion2 FROM vaers_symptoms WHERE symptomversion2 != '';
  197. INSERT INTO vaers_symptom (vaers_id, symptom, symptomversion)
  198. SELECT vaers_id, symptom3, symptomversion3 FROM vaers_symptoms WHERE symptomversion3 != '';
  199. INSERT INTO vaers_symptom (vaers_id, symptom, symptomversion)
  200. SELECT vaers_id, symptom4, symptomversion4 FROM vaers_symptoms WHERE symptomversion4 != '';
  201. INSERT INTO vaers_symptom (vaers_id, symptom, symptomversion)
  202. SELECT vaers_id, symptom5, symptomversion5 FROM vaers_symptoms WHERE symptomversion5 != '';
  203. DROP TABLE vaers_symptoms;
  204.  
  205. /* From here, the data should be in good enough shape to query it. */
  206.  
  207. /* For example, look at the number of reported deaths by age */
  208. WITH covid_vax AS (
  209.     SELECT DISTINCT vaers_id
  210.     FROM vaers_vax
  211.     WHERE vax_type='COVID19'
  212. ),
  213. covid_vax_deaths AS (
  214.     SELECT
  215.         vaers_data.*
  216.     FROM covid_vax JOIN vaers_data ON covid_vax.vaers_id = vaers_data.vaers_id
  217.     WHERE vaers_data.died = 1
  218. )
  219. SELECT age_yrs, COUNT(*)
  220. FROM covid_vax_deaths
  221. GROUP BY age_yrs;
  222.  
  223. /* Or look at the most common symptoms associated with death */
  224. SELECT
  225.     s.symptom,
  226.     COUNT(DISTINCT d.vaers_id) AS n_cases,
  227.     COUNT(DISTINCT CASE WHEN d.died = 1 THEN d.vaers_id ELSE NULL END) AS n_deaths
  228. FROM vaers_data d
  229.     JOIN vaers_symptom s ON d.vaers_id = s.vaers_id
  230.     JOIN vaers_vax v ON d.vaers_id = v.vaers_id
  231. WHERE v.vax_type = 'COVID19'
  232. GROUP BY s.symptom
  233. HAVING n_cases > 100
  234. ORDER BY 1.0 * n_deaths / n_cases DESC
  235. LIMIT 10;
  236.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!