*****************************************************************************************;
** Copyright (c) 2015 Center for Social Services Research,                             **;
** University of California at Berkeley. All rights reserved.                          **;
**                                                                                     **;
** This program produces reports for Permanency Performance Area 5                     **;
**                                                                                     **;
** Permanency Performance Area 5: Placement Stability                                  **;
** Measure: Of all children who enter foster care in a 12-month period, what is the    **;
** rate of placement moves per day of foster care?                                     **;
**                                                                                     **;
** Denominator: The denominator is, of children who enter foster care in a 12-month    **;
** period, the total number of days these children were in foster care as of the end   **;
** of the 12-month period. Children in care less that 8 days or who enter care at age  **;
** 18 or more are excluded from the denominator.                                       **;
**                                                                                     **;
** Numerator: The numerator is, among children in the denominator, the total number of **;
** placement moves during the 12-month period. The initial placement in foster care    **;
** (removal from home) is not counted, but all subsequent moves occurring within the   **;
** 12-month period are included in the calculation.                                    **;
**                                                                                     **;
** Children in foster care for less than 8 days and other settings a child may be      **;
** placed in, that are not considered as placement settings for AFCARS purposes, such  **;
** as trial home visits, are not included in this indicator.                           **;
**                                                                                     **;
** Performance: Performance for this measure is the numerator divided by the           **;
** denominator, expressed as a rate per 1,000 days. The rate is multiplied by 1,000 to **;
** produce a whole number which is easier to interpret. A decrease in the rate per     **;
** 1,000 days indicates an improvement in performance.                                 **; 
**                                                                                     **;
** Adapted for new CFSR measures by Joe Magruder                                       **;
**                                                                                     **;
**  Revision History                                                                   **:
**                                                                                     **;
*****************************************************************************************;

%macro age(date,birth);
  floor ((intck('month',&birth,&date)
  - (day(&date) < day(&birth))) / 12)
%mend age;

/*create smaller working file*/
data moves_01 
        (keep = fkclient_t temp_id age pe_s_dt pe_e_dt oh_s_dt oh_e_dt spell 
        plcmnt agy_rspc cnty_rem birth_dt gender_cd cens_rc ethnic hisp_cd hisp_cdx p_ethnctyc 
        plcg_rnc agency FLAG8 P_PLC ethnic gender_cd age18 tpe_e_dt ucb_plcmnt plc_fclc/*cnty*/);
    set dwh.ucb_fc_afcars(rename=(ethnic=eth gender_cd=gender)); 

    	/*add to exclude runaway incidents as placements*/
    if plc_fclc in(7164,7181,1420,6362,6363,6364,6365,6366,6367,6368,6369,6370,6371,7180) then delete;

   /*following has no effect unless program run against UCB_FC rather than UCB_FC_AFCARS*/
   if agy_rspc in (35, 6133, 6134) then delete;

   * agency responsible type *;

   if agy_rspc = 34 then agency = 1;
   else if agy_rspc = 33 or agy_rspc = 5603 then agency = 2;
   else agency = 3; 

    age18 = intnx('year', birth_dt, 18, 'sameday'); 

    /*cutoff at age 18*/
    if pe_e_dt ge age18 or pe_e_dt = . then tpe_e_dt = age18;
        else tpe_e_dt = pe_e_dt;

    /*limit to episodes starting before age 18 [and 8 days or more in length]
	  and to placements starting within a year of episode start (because measure
	  just looks at placements occuring within the 12-month period during which
	  the child entered care*/        
    if oh_s_dt le intnx('year', pe_s_dt,1, 'sameday')
        and oh_s_dt < age18
        and pe_s_dt < age18
     /* and (pe_e_dt - pe_s_dt ge 8 or pe_e_dt = .) */;
    
    /* age at placement episode start*/
    age = %age(pe_s_dt, birth_dt);
        if BIRTH_DT > PE_S_DT then age = 99;

   timeic = pe_e_dt - pe_s_dt;
   if timeic ge 8 or pe_e_dt = . then flag8=1;
   else flag8=2;

  /*assign placement facility type*/
  select(PLC_FCLC);
      when('2222') P_PLC = 1;            /* Pre-Adopt */
      when('1421','1422') P_PLC = 2;     /* Kin */
      when('1415','1416') P_PLC = 3;     /* Foster */
      when('1414', '2200') P_PLC = 4  ;  /* FFA */
      when('1419') P_PLC = 9;            /* Court Specified Home */
      when('7208') P_PLC = 9.2;          /* Tribally Approved Home */
      when('1417') P_PLC = 10;           /* Group */
      when('1418','7027') P_PLC = 11;    /* Shelter */
      when('5411') P_PLC = 14;           /* Guardian */
      when(' ','0','.') P_PLC = 99;      /* Missing */
      otherwise P_PLC = 29;              /* Other */
   end;
   ** Assign Ethnicity Class **;
   select(P_ETHNCTYC) ;
      when(.,0,6351,6352,6453) ETHNIC=99 ;          * Missing *;
      when(823,826) ETHNIC=1 ;                      * Black *;
      when(839,840,841,842,843,844) ETHNIC=2 ;      * White *;
      when(830,3162,3163,3164,3165) ETHNIC=3 ;      * Hispanic *;
      when(820,821) ETHNIC=5 ;                      * Native Am *;
      when(5922,5923,822,824,825,827,828,829, 831,
           832,833,834,835,836,837,838) ETHNIC=4 ;  * Asian/PI *;
      otherwise ETHNIC=99 ;                         * Invalid Value *;
   end ;
   if HISP_CD eq 'Y' then ETHNIC=3 ;                * Hispanic *;

   if gender='M' then gender_cd=2;
   else if gender='F' then gender_cd=1;
   else if gender='I' then gender_cd=3;
   else gender_cd=99;

    format tpe_e_dt age18 mmddyy10.;
	
   temp_id = FKCLIENT_T || compress(put(SPELL,Z2.));


    run;


**now move to specific periods as a macro;

%macro ENTRY(sq,sy,eq,ey);

*sq = start quarter, sy = start year, eq = end quarter, ey = end year;

%let empty=0;

%do SYEAR = &sy. %to &ey. ;

*** The following statements select the appropriate quarters for each year ***;

data _null_;

%if &sq. ^= 1 & &SYEAR.=&sy. %then
%do;

*** if the starting quarter is not 1 then process accordingly ***;

%if &sy.=&ey. %then
    %do;
    start_qtr=&sq. ;
    end_qtr=&eq. ;
    %end;

    %else
    %do;
    start_qtr=&sq. ;
    end_qtr=4 ;
    %end;

%end;

%else %if &eq. ^= 4 & &SYEAR.=&ey. %then
%do;

*** If the ending quarter is not 1 then process accordingly ***;

%if &sy.=&ey. %then
    %do;
    start_qtr=&sq. ;
    end_qtr=&eq. ;
    %end;

    %else
    %do;
    start_qtr=1 ;
    end_qtr=&eq. ;
    %end;

%end;

%else
%do;

*** Otherwise standard quarters ***;

start_qtr=1 ;
end_qtr=4;
%end;

call symput('stqtr',start_qtr);
call symput('enqtr',end_qtr);

run;

%do SQTR = &stqtr. %to &enqtr. ;


/* test time periods
%let spdate='01JAN2013'D;
%let sdate='01JAN2014'D;
%let edate='01JAN2015'D;
%let syear = 2014;
%let sqtr = 1;
*/
/*find all children entering care in the 12-month study period and all placements
  that started in that 12-month period*/
/*Note: effect of oh_s_dt limit is to limit placement counts to placements beginning
  before end of study period. moves_01 only includes placements starting before age 18*/
data moves_02;
    set moves_01;
    
    where PE_S_DT between yyq(&SYEAR,&SQTR) and yyq(&SYEAR+1,&SQTR)-1 and 
          OH_S_DT between yyq(&SYEAR,&SQTR) and yyq(&SYEAR+1,&SQTR)-1;

    run;

proc sort data = moves_02;
    by fkclient_t spell plcmnt;
    run;

/*calculate time in care before end of period or age 18, whichever comes first*/
/*note: tpe_e_dt [calculated for moves_01] = pe_e_dt or age_18, whichever comes first*/
/*note: ype_e_dt = end of period, age_18, or pe_e_dt, whichever comes first*/
data moves_03;
    set moves_02;
    by fkclient_t spell plcmnt;
    if last.spell;
    if tpe_e_dt ge yyq(&SYEAR+1,&SQTR)
       then ype_e_dt = yyq(&SYEAR+1,&SQTR);
       else ype_e_dt = tpe_e_dt;
    *if tpe_e_dt ge yyq(&SYEAR+1,&SQTR)
       then tic = yyq(&SYEAR+1,&SQTR) - pe_s_dt;
       *else tic = tpe_e_dt - pe_s_dt;
	tic = ype_e_dt - pe_s_dt;

    moves = plcmnt - 1;
    moves_ucb = ucb_plcmnt - 1;

	PERIOD_DT=yyq(&SYEAR,&SQTR);
	format period_dt ype_e_dt mmddyy10.;

    run;


/*limit to those between 0 and 17 years of age - redundant*/
data moves_04;
set moves_03;

where AGE between 0 and 17;

run;

/*find county/office assignment at start of placement episode, exlcuding
  emergency response offices*/


/*Bring in office assignment data*/
proc sql;
	create table entries_02 as
		select moves_04.*,
			ucb_office.*
			from moves_04 left join dwh.ucb_office
		on ucb_office.fkchld_clt = moves_04.fkclient_t
			and (ucb_office.office_s_dt < ype_e_dt /*should this be =< pe_e_dt?*/
				or ype_e_dt = .)
			and ucb_office.office_e_dt > pe_s_dt /*should this be => pe_s_dt?*/
		order by fkclient_t, temp_id, office_s_dt;
		;
	quit;

/*Not all records will have office assignment data - especially probation records
  from before about 2010. Separate those that do (entries_02a) from those that 
  don't (entries_02b)*/
data entries_02a 
		entries_02b (drop = fkchld_clt cnty_spfcd office_s_dt office_e_dt type);
	set entries_02;
	if office_s_dt ne . then output entries_02a;
		else output entries_02b;
	run;
/*
proc print data = entries_02a (obs = 10); 
	where year(tpe_e_dt) ge 2013;
	run;*/

/*for those with no office data, see if the State ID county data identifies
  a county with start and end dates*/

proc sql;
	create table entries_02b1 as
		select entries_02b.*,
			st_id.*
			from entries_02b left join dwh.st_id
		on st_id.fkchld_clt = entries_02b.fkclient_t
			and (st_id.office_s_dt < ype_e_dt /*should this be =< pe_e_dt?*/
				or ype_e_dt = .)
			and st_id.office_e_dt > pe_s_dt /*should this be => pe_s_dt?*/
		order by fkclient_t, temp_id, office_s_dt;
		;
	quit;

/*Put two files back together - i.e., add state ID county derrived identifiers
  to those records in entries_02 that are lacking cnty_spfcd, office_s_dt and
  office_e_dt data*/
/*At this point there are multiple records for many temp_ids - i.e., multiple
  office (or just county) assignments during a given placement episodes)*/
data entries_03;
	set entries_02a entries_02b1;
	run;
/*
proc print data = entries_03 (obs = 10);
	run;
*/
proc sort data = entries_03;
	by fkclient_t temp_id office_s_dt;
	run;
/*
proc print data = entries_03 (obs = 35); 
	var temp_id cnty_spfcd cnty agency type pe_s_dt pe_e_dt office_s_dt office_e_dt p_plc;
	by fkchld_clt;
	*where pe_e_dt ge '01JAN2000'D and cnty_spfcd = ' ';
	*where agency = 2;
	*where cnty_spfcd = '07';
	*where cws_off_no in ("S1278");
	run;
*/

/*delete hotline/emergency response office case assignments in Los Angeles when
  subsequent office assignments exist*/
/*this could be expanded to include similar offices in other counties*/
/*delete referral office if there also is a case office unless it is the
  last assignment
  This follows methodology of using county/office assignment of case unless
  there only is referral data*/
data entries_04;
	set entries_03;
	by fkclient_t temp_id office_s_dt;
	if cws_off_no in ("S1278") and last.temp_id = 0 then delete;
	if type = "R" and last.temp_id = 0 then delete;
	if office_e_dt = '31DEC3000'D then office_e_dt = .;
	run;
/*
proc print data = entries_04 (obs = 80);
	*var fkclient_t pe_s_dt pe_e_dt oh_s_dt office_s_dt office_e_dt type cws_off_no cws_off_nm;
	var temp_id agency type cnty_spfcd pe_s_dt pe_e_dt ype_e_dt office_s_dt office_e_dt cws_off_no;
	where (pe_e_dt = . or pe_e_dt ge '01JAN2005'D) and  cnty_spfcd ne "19";;
	*where fkclient_t = "002hF9o668";
	run;
*/
/*find first office assignment and last office assignment for each placement
  episode. Note temp_id is unique for each child in each episode.*/
/*first office assignment*/
data entries_04_first (drop = oh_s_dt fkchld_clt 
		rename= (cnty_spfcd = f_cnty_spfcd cws_off_no = f_cws_off_no 
		fkcws_offt = f_fkcws_offt type = f_type
		office_s_dt = f_office_s_dt office_e_dt = f_office_e_dt));
	set entries_04;
	by fkclient_t temp_id office_s_dt;
	if first.temp_id;
	label type = 'First Type'
		cnty_spfcd = 'First County' 
		cws_off_no = 'First Office'
		fkcws_offt = 'First Office ID'
		office_s_dt = 'First Office Start Date'
		office_e_dt = 'First Office End Date'
		l_scpr = "Last SCP Relationship"
		l_plc = "Last Placement Type"
		f_plc = "First Placement Type"
		f_scpr = "First SCP Relationship"
		cnty = "Removal County";
	run;

data entries_04_last (keep = fkclient_t temp_id 
		cnty_spfcd cws_off_no fkcws_offt office_s_dt office_e_dt type oh_s_dt oh_e_dt
		/*f_scpr f_plc l_scpr l_plc*/
		rename= (cnty_spfcd = l_cnty_spfcd cws_off_no = l_cws_off_no 
		fkcws_offt = l_fkcws_offt type = l_type
		office_s_dt = l_office_s_dt office_e_dt = l_office_e_dt));
	set entries_04;
	by fkclient_t temp_id office_s_dt;
	if last.temp_id;
	label type = 'Last Type'
		cnty_spfcd = 'Last County' 
		cws_off_no = 'Last Office'
		fkcws_offt = 'Last Office ID'
		office_s_dt = 'Last Office Start Date'
		office_e_dt = 'Last Office End Date';
	run;

/*Merge files together. Modify placement counter. 
  When no county/office assignment and no state id county, then use
  removal county as first county. This affects early CWS/CMS records and
  some more recent probation records, almost all from Los Angeles*/
data entries ;
	merge entries_04_first entries_04_last;
	by fkclient_t temp_id;
/* Adding Placement Count Indicator */
   if PLCMNT = 1 then PLACE = 1;
   else if PLCMNT = 2 then PLACE =2;
   else if PLCMNT = 3 then PLACE =3;
   else if PLCMNT = 4 then PLACE =4;
   else if PLCMNT >= 5 then PLACE = 5;
	label place = "Modified Placement Counter";
    if f_cnty_spfcd = '  ' then 
		f_cnty_spfcd = put((cnty), Z2.);
    if l_cnty_spfcd = '  ' then 
		l_cnty_spfcd = put((cnty), Z2.);
	*run;


	F_CNTY = input(f_cnty_spfcd, best2.);
		if 1 <= F_CNTY <= 58 then CNTY = F_CNTY;
		else CNTY = 98;

        if cnty = 19 then do;
                     if f_cws_off_no in("S3253") then COUNTY = 1901; /*Belvedere*/
                else if f_cws_off_no in("S1277") then COUNTY = 1902; /*Compton*/
                else if f_cws_off_no in("S1280") then COUNTY = 1903; /*El Monte*/
                else if f_cws_off_no in("S1254") then COUNTY = 1904; /*Glendora*/

                else if f_cws_off_no in("S2218") then COUNTY = 1905; /* Hawthorne old Wateridge South*/

                else if f_cws_off_no in("S8234") then COUNTY = 1906; /*Lancaster*/
                else if f_cws_off_no in("S3239") then COUNTY = 1907; /*Metro North*/
                else if f_cws_off_no in("S8236") then COUNTY = 1908; /*Palmdale*/
                else if f_cws_off_no in("S5252") then COUNTY = 1909; /*Pasadena*/
                else if f_cws_off_no in("S1255") then COUNTY = 1910; /*Pomona*/

                else if f_cws_off_no in("S8251") then COUNTY = 1911; /*Santa Clarita*/
                else if f_cws_off_no in("S4261") then COUNTY = 1912; /*Santa Fe Springs*/
                else if f_cws_off_no in("S7207") then COUNTY = 1913; /*South County*/
                else if f_cws_off_no in("S2213") then COUNTY = 1914; /*Torrance*/
                else if f_cws_off_no in("S5211") then COUNTY = 1915; /*Van Nuys*/

                else if f_cws_off_no in("S6219") then COUNTY = 1916; /*Vermont Corridor*/
                else if f_cws_off_no in("S2217") then COUNTY = 1917; /*Wateridge old Wateridge North*/

               *else if f_cws_off_no in("S2218") then COUNTY = 1917; /*Wateridge South*/

                else if f_cws_off_no in("S6260") then COUNTY = 1918; /*West Los Angeles*/
                else if f_cws_off_no in("S5212") then COUNTY = 1919; /*West SF Valley*/
                else if f_cws_off_no in("S0216") then COUNTY = 1920; /*Adoptions*/
                else if f_cws_off_no in("S0222") then COUNTY = 1924; /*Medical & ASFA*/
                else if f_cws_off_no in("S1251") then COUNTY = 1931; /*American Indian*/
                else if f_cws_off_no in("S1250") then COUNTY = 1932; /*Asian Pacific Project*/
                else if f_cws_off_no in("S0249") then COUNTY = 1933; /*Deaf Services*/
                else if f_cws_off_no in("S0237") then COUNTY = 1934; /*Pomona Family First*/
                else if f_cws_off_no in("S1288") then COUNTY = 1935; /*CSEC*/
                else if /*f_cws_off_no in("S1278") and */
                     f_fkcws_offt = "1iyrzAg196" then COUNTY = 1940; /*ROU*/
                else if f_cws_off_no in("S0224") then COUNTY = 1941; /*MART*/
                else if /*f_cws_off_no in("S1278") and */
                          f_fkcws_offt in("PHmifFE197","QxMGV6D197")
                                                 then COUNTY = 1942; /*ER Command/Hotline*/


             /* else if f_cws_off_no in("S1278") then COUNTY = 1940; */ /*ERCP*/
             /* else if f_cws_off_no in("S0224") then COUNTY = 1941; */ /*MART*/
             /* else if f_cws_off_no in("S1278") then COUNTY = 1942; */ /*Hotline*/

                else if f_cws_off_no in("S1286") then COUNTY = 1943; /*Juvenile Court Services*/
                else if f_cws_off_no in("S0246", "S0247", "S9056", 
                             "S4261", "S0394") then COUNTY = 1945; /*Administration*/
                else if f_cws_off_no in("S0299") then COUNTY = 1950; /*Probation*/
                else if f_cws_off_no in("S9072") then COUNTY = 1960; /*KinGAP*/
                else                                COUNTY = 1999; /*missing*/
        end;
        
        else if cnty = 01 then COUNTY = 0100;
        else if cnty = 02 then COUNTY = 0200;
        else if cnty = 03 then COUNTY = 0300;
        else if cnty = 04 then COUNTY = 0400;
        else if cnty = 05 then COUNTY = 0500;
        else if cnty = 06 then COUNTY = 0600;
        else if cnty = 07 then COUNTY = 0700;
        else if cnty = 08 then COUNTY = 0800;
        else if cnty = 09 then COUNTY = 0900;
        else if cnty = 10 then COUNTY = 1000;
        else if cnty = 11 then COUNTY = 1100;
        else if cnty = 12 then COUNTY = 1200;
        else if cnty = 13 then COUNTY = 1300;
        else if cnty = 14 then COUNTY = 1400;
        else if cnty = 15 then COUNTY = 1500;
        else if cnty = 16 then COUNTY = 1600;
        else if cnty = 17 then COUNTY = 1700;
        else if cnty = 18 then COUNTY = 1800;
       *else if cnty = 19 then COUNTY = 1900;
        else if cnty = 20 then COUNTY = 2000;
        else if cnty = 21 then COUNTY = 2100;
        else if cnty = 22 then COUNTY = 2200;
        else if cnty = 23 then COUNTY = 2300;
        else if cnty = 24 then COUNTY = 2400;
        else if cnty = 25 then COUNTY = 2500;
        else if cnty = 26 then COUNTY = 2600;
        else if cnty = 27 then COUNTY = 2700;
        else if cnty = 28 then COUNTY = 2800;
        else if cnty = 29 then COUNTY = 2900;
        else if cnty = 30 then COUNTY = 3000;
        else if cnty = 31 then COUNTY = 3100;
        else if cnty = 32 then COUNTY = 3200;
        else if cnty = 33 then COUNTY = 3300;
        else if cnty = 34 then COUNTY = 3400;
        else if cnty = 35 then COUNTY = 3500;
        else if cnty = 36 then COUNTY = 3600;
        else if cnty = 37 then COUNTY = 3700;
        else if cnty = 38 then COUNTY = 3800;
        else if cnty = 39 then COUNTY = 3900;
        else if cnty = 40 then COUNTY = 4000;
        else if cnty = 41 then COUNTY = 4100;
        else if cnty = 42 then COUNTY = 4200;
        else if cnty = 43 then COUNTY = 4300;
        else if cnty = 44 then COUNTY = 4400;
        else if cnty = 45 then COUNTY = 4500;
        else if cnty = 46 then COUNTY = 4600;
        else if cnty = 47 then COUNTY = 4700;
        else if cnty = 48 then COUNTY = 4800;
        else if cnty = 49 then COUNTY = 4900;
        else if cnty = 50 then COUNTY = 5000;
        else if cnty = 51 then COUNTY = 5100;
        else if cnty = 52 then COUNTY = 5200;
        else if cnty = 53 then COUNTY = 5300;
        else if cnty = 54 then COUNTY = 5400;
        else if cnty = 55 then COUNTY = 5500;
        else if cnty = 56 then COUNTY = 5600;
        else if cnty = 57 then COUNTY = 5700;
        else if cnty = 58 then COUNTY = 5800;
        else if cnty = 98 then COUNTY = 9800;




    run;



proc freq data = entries;
	table county;
	run;



proc sort data = entries;
	by fkclient_t temp_id;
	run;



data moves_05;
set entries;

AGENCY = 4;

run;

data moves_06;
set entries moves_05;
run;

proc sql;

create table p5_cnty_&SYEAR.Q&SQTR  as
select CNTY length=3, AGE length=3, GENDER_CD length=3, ETHNIC length=3, FLAG8 length=3,
       PERIOD_DT length=4, AGENCY length=3, P_PLC, /* MOVES length=8, TIC length=8, */
       sum(MOVES) as NUM length=4 format=comma12., sum(TIC) as DENOM length=6 format=comma12.,
	   f_fkcws_offt, COUNTY
from moves_06 
group by CNTY, AGENCY, AGE, GENDER_CD, ETHNIC, FLAG8, P_PLC, /* MOVES, TIC, NUM, DENOM, */ PERIOD_DT,
		f_fkcws_offt, COUNTY;

quit;

data moves_07;
set moves_06(drop=CNTY);

CNTY = 0;

run;

proc sql;

create table p5_state_&SYEAR.Q&SQTR as
select CNTY length=3, AGE length=3, GENDER_CD length=3, ETHNIC length=3, FLAG8 length=3,
       PERIOD_DT length=4, AGENCY length=3, P_PLC, /* MOVES length=8, TIC length=8, */
       sum(MOVES) as NUM length=4 format=comma12., sum(TIC) as DENOM length=6 format=comma12.,
	   f_fkcws_offt, COUNTY
from moves_07
group by CNTY, AGENCY, AGE, GENDER_CD, ETHNIC, FLAG8, P_PLC, /* MOVES, TIC, NUM, DENOM, */ PERIOD_DT,
	   f_fkcws_offt, COUNTY;

quit;

proc append base=county data=p5_cnty_&SYEAR.Q&SQTR;
proc append base=state data=p5_state_&SYEAR.Q&SQTR;

%end;
%end;
%mend ENTRY;

%ENTRY(1,1998,1,2024);

data p5;
set county state;

run;

proc sql;

create table p5_1 as
select CNTY, COUNTY, AGE, GENDER_CD, ETHNIC, FLAG8, PERIOD_DT, AGENCY, P_PLC, F_FKCWS_OFFT,
       sum(NUM) as NUM length=4 format=comma12., sum(DENOM) as DENOM length=6 format=comma12.
from p5
where CNTY = 19
group by CNTY, COUNTY, AGE, GENDER_CD, ETHNIC, FLAG8, PERIOD_DT, AGENCY, P_PLC, F_FKCWS_OFFT ;

quit;

data p5_2;
set p5_1(drop = CNTY COUNTY);

COUNTY = 1900;
CNTY = 19;

run;

data dvlp.p5;
set p5 p5_2;

if CNTY = 19 and COUNTY = . then COUNTY = 1999;
else if COUNTY = . then COUNTY = 9800;

if CNTY = 00 then COUNTY = 0000;

run;

proc sort data = dvlp.p5;
by AGENCY CNTY COUNTY PERIOD_DT;
run;

data test.p5;
set dvlp.p5;

run;