READING A MULTILEVEL DATA FILE INTO SAS

Click here to download SAS code

The following code converts FBI NIBRS data from ASCII format to a large SAS flat file. When using this code, be sure to insert the path and file name of the data file to be used below, as well as the file name for saving. If you need any assistance working with the syntax provided, please contact us.

The assumptions for the following example of code are:

The NIBRS data file was obtained on tapes from the FBI in ASCII format; and
The NIBRS data segments that comprise a single incident report can be linked by a combination of the ORI data field plus the incident number data field (incident numbers are encrypted).

filename in 'Directory:\Path\Filename.txt';
libname out 'Directory:\Path\ ';
OPTIONS LINESIZE=80 FIRSTOBS=1 OBS=MAX;
DATA OUT.Filename;
INFILE IN LRECL=300 MISSOVER;
input @1 RECORD_TYPE $char2. @;

RECORD_TYPE EQ '01' in defines the NIBRS Offense Group "A" administrative segment as the first record type to be read. Variables are created for incident year, month, and day because the tape only contains the incident date in YYYYMMDD format. All other variables are read from the FBI NIBRS tape. The data variable is then formatted.

IF (RECORD_TYPE EQ '01') THEN DO;

INPUT	@3	ST_CODE	$CHAR2.
	@5	ORI	$CHAR9.
	@14	INC_NUM	$CHAR12.
	@26	INC_DATE	YYMMDD8.
	@26	INC_YR	4.
	@30	INC_MO	2.
	@32	INC_DY	2.
	@34	RPTDATE	$CHAR1.
	@35	INC_HR	2.
	@37	TOT_OSEG	2.
	@39	TOT_VSEG	3.
	@42	TOT_DSEG	2.
	@44	TOT_ASEG	2.
	@46	CITY_SUB	$CHAR4.
	@50	EXCP_CLR	$CHAR1.
	@51	EXCLR_YR	4.
	@55	EXCLR_MO	2.
	@57	EXCLR_DY	2.
	@59	INCOFF1	$CHAR3.
	@62	INCOFF2	$CHAR3.
	@65	INCOFF3	$CHAR3.
	@68	INCOFF4	$CHAR3.
	@71	INCOFF5	$CHAR3.
	@74	INCOFF6	$CHAR3.
	@77	INCOFF7	$CHAR3.
	@80	INCOFF8	$CHAR3.
	@83	INCOFF9	$CHAR3.
	@86	INCOFF10	$CHAR3. ;

FORMAT INC_DATE YYMMDD10. ;
OUTPUT OUT.Filename;
END;

RECORD_TYPE EQ '02' defines the NIBRS Offense Group "A" offense segment as the second record type to be read.

IF (RECORD_TYPE EQ '02') THEN DO;

INPUT	@3	ST_CODE	$CHAR2.
	@5	ORI	$CHAR9.
	@14	INC_NUM	$CHAR12.
	@26	INC_DATE	YYMMDD8.
	@26	INC_YR	4.
	@30	INC_MO	2.
	@32	INC_DY	2.
	@34	OFF_CODE	$CHAR3.
	@37	ATT_COMP	$CHAR1.
	@38	SUSPUSE1	$CHAR1.
	@39	SUSPUSE2	$CHAR1.
	@40	SUSPUSE3	$CHAR1.
	@41	OFF_LOC	$CHAR2.
	@43	PREM_ENT	$CHAR2.
	@45	METH_ENT	$CHAR1.
	@46	CRIMACT1	$CHAR1.
	@47	CRIMACT2	$CHAR1.
	@48	CRIMACT3	$CHAR1.
	@49	WEAPON1	$CHAR2.
	@51	AUTOWPN1	$CHAR1.
	@52	WEAPON2	$CHAR2.
	@54	AUTOWPN2	$CHAR1.
	@55	WEAPON3	$CHAR2.
	@57	AUTOWPN3	$CHAR1.
	@58	BIAS	$CHAR2. ;

FORMAT INC_DATE YYMMDD10. ;
OUTPUT OUT.Filename;
END;

RECORD_TYPE EQ '03' defines the NIBRS Offense Group "A" property segment as the third record type to be read.

IF (RECORD_TYPE EQ '03') THEN DO;

INPUT	@3	ST_CODE	$CHAR2.
	@5	ORI	$CHAR9.
	@14	INC_NUM	$CHAR12.
	@26	INC_DATE	YYMMDD8.
	@26	INC_YR	4.
	@30	INC_MO	2.
	@32	INC_DY	2.
	@34	LOSSTYPE	$CHAR1.
	@35	P_DESC	$CHAR2.
	@37	P_VALU	9.
	@46	P_DATE	YYMMDD8.
	@54	MVT_NUM	2.
	@56	MVT_RECO	2.
	@58	DRUGTYP1	$CHAR1.
	@59	WHOLQUA1	9.
	@68	FRACQUA1	3.
	@71	TYPMEAS1	$CHAR2.
	@73	DRUGTYP2	$CHAR1.
	@74	WHOLQUA2	9.
	@83	FRACQUA2	3.
	@86	TYPMEAS2	$CHAR2.
	@88	DRUGTYP3	$CHAR1.
	@89	WHOLQUA3	9.
	@98	FRACQUA3	3.
	@101	TYPMEAS3	$CHAR2.
	@103	P_OFF1	$CHAR3.
	@106	P_OFF2	$CHAR3.
	@109	P_OFF3	$CHAR3.
	@112	P_OFF4	$CHAR3.
	@115	P_OFF5	$CHAR3.
	@118	P_OFF6	$CHAR3.
	@121	P_OFF7	$CHAR3.
	@124	P_OFF8	$CHAR3.
	@127	P_OFF9	$CHAR3.
	@130	P_OFF10	$CHAR3. ;

     FORMAT INC_DATE
                      P_DATE                     YYMMDD10.    ;
     OUTPUT OUT.Filename;
END;

RECORD_TYPE EQ '04' defines the NIBRS Offense Group "A" victim segment as the fourth record type to be read.

IF (RECORD_TYPE EQ '04') THEN DO;

INPUT	@3	ST_CODE	$CHAR2.
	@5	ORI	$CHAR9.
	@14	INC_NUM	$CHAR12.
	@26	INC_DATE	YYMMDD8.
	@26	INC_YR	4.
	@30	INC_MO	2.
	@32	INC_DY	2.
	@34	VIC_NUM	3.
	@37	V_OFF1	$CHAR3.
	@40	V_OFF2	$CHAR3.
	@43	V_OFF3	$CHAR3.
	@46	V_OFF4	$CHAR3.
	@49	V_OFF5	$CHAR3.
	@52	V_OFF6	$CHAR3.
	@55	V_OFF7	$CHAR3.
	@58	V_OFF8	$CHAR3.
	@61	V_OFF9	$CHAR3.
	@64	V_OFF10	$CHAR3.
	@67	V_TYPE	$CHAR1.
	@68	VIC_AGE	$CHAR2.
	@70	VIC_SEX	$CHAR1.
	@71	V_RACE	$CHAR1.
	@72	V_ETHNIC	$CHAR1.
	@73	V_RESID	$CHAR1.
	@74	VCIRCUM1	$CHAR2.
	@76	VCIRCUM2	$CHAR2.
	@78	VJUSTHOM	$CHAR1.
	@79	V_INJ1	$CHAR1.
	@80	V_INJ2	$CHAR1.
	@81	V_INJ3	$CHAR1.
	@82	V_INJ4	$CHAR1.
	@83	V_INJ5	$CHAR1.
	@84	OFNSEQ1	2.
	@86	OVR1	$CHAR2.
	@88	OFNSEQ2	2.
	@90	OVR2	$CHAR2.
	@92	OFNSEQ3	2.
	@94	OVR3	$CHAR2.
	@96	OFNSEQ4	2.
	@98	OVR4	$CHAR2.
	@100	OFNSEQ5	2.
	@102	OVR5	$CHAR2.
	@104	OFNSEQ6	2.
	@106	OVR6	$CHAR2.
	@108	OFNSEQ7	2.
	@110	OVR7	$CHAR2.
	@112	OFNSEQ8	2.
	@114	OVR8	$CHAR2.
	@116	OFNSEQ9	2.
	@118	OVR9	$CHAR2.
	@120	OFNSEQ10	2.
	@122	OVR10	$CHAR2. ;

FORMAT INC_DATE YYMMDD10. ;
OUTPUT OUT.Filename;
END;

RECORD_TYPE EQ '05' defines the NIBRS Offense Group "A" offender segment as the fifth record type to be read.

IF (RECORD_TYPE EQ '05') THEN DO;

INPUT	@3	ST_CODE	$CHAR2.
	@5	ORI	$CHAR9.
	@14	INC_NUM	$CHAR12.
	@26	INC_DATE	YYMMDD8.
	@26	INC_YR	4.
	@30	INC_MO	2.
	@32	INC_DY	2.
	@34	OFNSEQ	2.
	@36	OFF_AGE	2.
	@38	OFF_SEX	$CHAR1.
	@39	OFF_RACE	$CHAR1. ;

FORMAT INC_DATE YYMMDD10. ;
OUTPUT OUT.Filename;
END;

RECORD_TYPE EQ '06' in combination with DATA LIST defines the NIBRS Offense Group "A" arrestee segment as the sixth record type to be read.

IF (RECORD_TYPE EQ '06') THEN DO;

INPUT	@3	ST_CODE	$CHAR2.
	@5	ORI	$CHAR9.
	@14	INC_NUM	$CHAR12.
	@26	INC_DATE	YYMMDD8.
	@26	INC_YR	4.
	@30	INC_MO	2.
	@32	INC_DY	2.
	@34	ARRSEQ	2.
	@36	ATR	$CHAR12.
	@48	ARR_YR	4.
	@52	ARR_MO	2.
	@54	ARR_DY	2.
	@56	ARR_TYPE	$CHAR1.
	@57	MSG	$CHAR1.
	@58	ARR_OFF	$CHAR3.
	@61	ARR_WPN1	$CHAR2.
	@63	AUTOWPN1	$CHAR1.
	@64	ARR_WPN2	$CHAR2.
	@66	AUTOWPN2	$CHAR1.
	@67	ARR_AGE	2.
	@69	ARR_SEX	$CHAR1.
	@70	ARR_RACE	$CHAR1.
	@71	ARR_ETHN	$CHAR1.
	@72	ARR_RES	$CHAR1.
	@73	ARR_DISP	$CHAR1.
	@74	ARR_CLR	$CHAR1.
	@75	ARROFF1	$CHAR3.
	@78	ARROFF2	$CHAR3.
	@81	ARROFF3	$CHAR3.
	@84	ARROFF4	$CHAR3.
	@87	ARROFF5	$CHAR3.
	@90	ARROFF6	$CHAR3.
	@93	ARROFF7	$CHAR3.
	@96	ARROFF8	$CHAR3.
	@99	ARROFF9	$CHAR3.
	@102	ARROFF10	$CHAR3. ;

FORMAT INC_DATE YYMMDD10. ;
OUTPUT OUT.Filename;
END;

RECORD_TYPE EQ '07' defines the NIBRS Offense Group "B" arrestee segment as the seventh record type to be read.

IF (RECORD_TYPE EQ '07') THEN DO;

INPUT	@3	ST_CODE	$CHAR2.
	@5	ORI	$CHAR9.
	@14	INC_NUM	$CHAR12.
	@26	INC_DATE	YYMMDD8.
	@26	INC_YR	4.
	@30	INC_MO	2.
	@32	INC_DY	2.
	@34	ARRSEQ	2.
	@36	CITY_SUB	$CHAR4.
	@40	ARR_TYPE	$CHAR1.
	@41	ARR_OFF	$CHAR3.
	@44	ARR_WPN1	$CHAR2.
	@46	AUTOWPN1	$CHAR1.
	@47	ARR_WPN2	$CHAR2.
	@49	AUTOWPN2	$CHAR1.
	@50	ARR_AGE	2.
	@52	ARR_SEX	$CHAR1.
	@53	ARR_RACE	$CHAR1.
	@54	ARR_ETHN	$CHAR1.
	@55	ARR_RES	$CHAR1.
	@56	ARR_DISP	$CHAR1. ;

FORMAT INC_DATE YYMMDD10. ;
OUTPUT OUT.Filename;
END;

The RUN command tells SAS to run the entire code and read each record type.

RUN;