aers

c.sql

各テーブルの yy と q は取得したファイルに合わせます。
例えば FAERS_ASCII_2013Q4.zip であれば

  • DEMO13Q4
  • DRUG13Q4
  • REAC13Q4
  • OUTC13Q4
  • RPSR13Q4
  • THER13Q4
  • INDI13Q4

のテーブルを作成することになる。
下記SQLは、そのテーブルを作成するSQL。基本新規のデータが発行されたらテキストエディッタの「置換」で対応すればOK

---- DEMOGRAPHIC file (DEMOyyQq.TXT)
CREATE TABLE DEMO13Q1 (
  PRIMARYID numeric(12),       --- ISR at AERS
  CASEID	numeric(12),   --- CASE at AERS
  CASEVERSION   numeric(12),
  I_F_CODE 	char(1),
---  FOLL_SEQ 	varchar(2),    --- obsolete
---  IMAGE 	varchar(15),   --- obsolete
  EVENT_DT 	varchar(8),    --- former DATE
  MFR_DT 	varchar(8),    --- former DATE
  INIT_FDA_DT   date,
  FDA_DT 	date,
  REPT_COD 	char(3),
  MFR_NUM 	varchar(100),
  MFR_SNDR 	varchar(60),
  AGE 		varchar(10),   --- former numeric(7)
  AGE_COD 	char(3),
  GNDR_COD 	char(3),
  E_SUB 	char(1),
  WT 		varchar(10),   --- former numeric(11)
  WT_COD 	char(5),
  REPT_DT 	varchar(8),    --- former date
  OCCP_COD 	char(5),
---  DEATH_DT 	date,          --- obsolete
  TO_MFR 	char(1),
---  CONFID 	char(1),       --- obsolete
  REPORTER_COUNTRY varchar(50),
  OCCR_COUNTRY  char(2),
  QTR		numeric(12) default 201301,    --- format yyyyqq
PRIMARY KEY(PRIMARYID)
);
---- DRUG file (DRUGyyQq.TXT)
CREATE TABLE DRUG13Q1 (
  PRIMARYID 	numeric(12),    --- ISR at AERS
  CASEID	numeric(12),    --- CASE at AERS
  DRUG_SEQ 	numeric(12),
  ROLE_COD 	char(2),
  DRUGNAME 	varchar(1024),  --- length from 300 to 1024
  VAL_VBM 	numeric(1),
  ROUTE 	varchar(30),
  DOSE_VBM 	varchar(300),
  CUM_DOSE_CHR  NUMBER,
  CUM_DOSE_UNIT varchar(7),
  DECHAL 	char(1),
  RECHAL 	char(1),
  LOT_NUM       varchar(1024),  --- length from 255 to 1024
  EXP_DT 	varchar(255),   --- length from 100 to 255
  NDA_NUM 	varchar(20),    --- length from 10 to 20
  DOSE_AMT      varchar(20),
  DOSE_UNIT     varchar(20),
  DOSE_FORM     varchar(50),
  DOSE_FREQ     varchar(20),
  QTR		numeric(12) default 201301    --- format yyyyqq
);
---
---- REACTION file (REACyyQq.TXT)
CREATE TABLE REAC13Q1 (
  PRIMARYID     numeric(12),    --- ISR at AERS
  CASEID	numeric(12),    --- CASE at AERS
  PT  		varchar(100),
  QTR		numeric(12) default 201301,    --- format yyyyqq
PRIMARY KEY(PRIMARYID,PT)
);
---- OUTCOME file (OUTCyyQq.TXT)
CREATE TABLE OUTC13Q1 (
  PRIMARYID     numeric(12),    --- ISR at AERS
  CASEID	numeric(12),    --- CASE at AERS
  OUTC_CODE 	char(2),
  QTR		numeric(12) default 201301,    --- format yyyyqq
PRIMARY KEY (PRIMARYID,OUTC_CODE)
);
---- REPORT SOURCE file (RPSRyyQq.TXT)
CREATE TABLE RPSR13Q1 (
  PRIMARYID     numeric(12),    --- ISR at AERS
  CASEID	numeric(12),    --- CASE at AERS
  RPSR_COD 	char(3),
  QTR		numeric(12) default 201301,    --- format yyyyqq
PRIMARY KEY(PRIMARYID,RPSR_COD)
);
---- THERAPY dates file (THERyyQq.TXT)
CREATE TABLE THER13Q1 (
  PRIMARYID     numeric(12),    --- ISR at AERS
  CASEID	numeric(12),    --- CASE at AERS
  DSG_DRUG_SEQ 	numeric(12),
  START_DT 	varchar(8),
  END_DT 	varchar(8),
  DUR  		varchar(10),
  DUR_COD 	varchar(10),
  QTR		numeric(12) default 201301    --- format yyyyqq
);
---- INDICATIONS for use file (INDIyyQq.TXT)
CREATE TABLE INDI13Q1 (
  PRIMARYID     numeric(12),    --- ISR at AERS
  CASEID	numeric(12),    --- CASE at AERS
  INDI_DRUG_SEQ numeric(12),
  INDI_PT 	varchar(100),
  QTR		numeric(12) default 201301    --- format yyyyqq
);
 
exit

トップ   編集 添付 複製 名前変更     ヘルプ   最終更新のRSS
Last-modified: 2014-09-04 (木) 11:30:47 (2417d)