7,612
edits
Changes
Jump to navigation
Jump to search
→NIH
===NIH===
The 2018 update process was:
*Visit https://exporter.nih.gov/ExPORTER_Catalog.aspx
*Retrieve 2016, 2017, and 2018 csvs to E:\projects\grants\NIH\Source Files
*SBIR/STTR Joint codes: RC3,SB1,UB1,UC3
*'''Total_Cost''' is only available in certain instances but is the best cost variable.
The 2022 update process was:
*Visit https://exporter.nih.gov/ExPORTER_Catalog.aspx
*Retrieve 2019, 2020, and 2021 csvs to E:\projects\grants\NIH\Source Files
*Open each in excel, copy and paste to a tab-delimited text file.
*Remove quotes from the file using regex (in textpad) and save as UTF8
*Run clean.py to fix the bad records
*Run LoadNIH.sql to load to the grants database (put input files in z:/bulk/grants)
Example processing code (for selected stats for Vermont):
CREATE TABLE nih_vt_stats AS
SELECT extract(year from award_notice_date) as year, direct_cost_amt+indirect_cost_amt as sum_amounts, total_cost, full_project_num,
CASE WHEN direct_cost_amt+indirect_cost_amt>total_cost THEN direct_cost_amt+indirect_cost_amt ELSE total_cost END AS amount,
CASE WHEN org_name LIKE 'UNIVERSITY OF VERMONT%' THEN 1 ELSE 0 END AS uvm
FROM nih WHERE org_state='VT';
--Updated Rows 9493
SELECT year, uvm, sum(amount), count(DISTINCT full_project_num)
FROM nih_vt_stats GROUP BY year, uvm ORDER BY uvm,year;
==grants.gov==
