*PIT_OUT.sas;

data point1a;
set dwh.ucb_pit;

AGENCY = 4;

run;

data point1;
set dwh.ucb_pit point1a;

run;

data point2;
set point1;

if ETHNIC not in (1,2,3,4,5) then ETHNIC = 99;


run;


proc sql;

create table county as
select cnty_spfcd, COUNTY length=4, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, CENS_RC, 
       CENS_ETHNIC, HISP_CDX, TIME_IN, PIT_PLC, PIT_SCPR, REMREAS, OH_LOC_PIT,
	  PERIOD_DT, count(*) as COUNT length=4 format=comma12.
from point2
group by cnty_spfcd, COUNTY, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, CENS_RC, CENS_ETHNIC, 
       HISP_CDX, TIME_IN, PIT_PLC, PIT_SCPR, REMREAS, OH_LOC_PIT, PERIOD_DT;
quit;

data point3a;
set point2;

COUNTY = 0;

run;

data point3b;
set point2;

where cnty_spfcd = '19'; 
COUNTY = 1900;

run;

data point3;
set point3a point3b;
run;

proc sql;
create table state as
select cnty_spfcd, COUNTY length=4, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, CENS_RC, 
       CENS_ETHNIC, HISP_CDX, TIME_IN, PIT_PLC, PIT_SCPR, REMREAS, OH_LOC_PIT, 
       PERIOD_DT, count(*) as COUNT length=4 format=comma12.
from point3
group by cnty_spfcd, COUNTY, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, CENS_RC, CENS_ETHNIC, 
       HISP_CDX, TIME_IN, PIT_PLC, PIT_SCPR, REMREAS, OH_LOC_PIT, PERIOD_DT;
quit;



data pit_la1;
set county state;

if PIT_PLC = . then PIT_PLC = 99;

run;

data test.pit;
set pit_la1; 

if cnty_spfcd = '19' and COUNTY = . then COUNTY = 1999;
else if COUNTY = . then COUNTY = 9800;

run;

proc sort data = test.pit;
by AGENCY COUNTY PERIOD_DT;
run;