*****************************************************************************************;
** Copyright (c) 2007 Center for Social Services Research,                             **;
** University of California at Berkeley. All rights reserved.                          **;
**                                                                                     **;
** This program produces reports for Permanency Performance Area 2 and 3               **;
**                                                                                     **;
** Permanency Performance Area 2 & 3: Permanency in 12 months for children in foster   **:
** care 12-23 months (Area 2) and 24 months or more (Area 3)                           **;
**                                                                                     **;
** Measure: Of all children in foster care on the first day of the 12-month period who **;
** had been in foster care (in that episode) between 12 and 23 months(Area 2) and 24   **;
** months or more (Area 3), what percent discharged from foster care to permanency     **;
** within 12 months of the first day of the 12-month period?                           **;
**                                                                                     **;
** Permanency Achieving permanency for children in foster care for long periods.       **;
** Of all children in foster care for 24 months or longer on the first day of the      **;
** period, what percent were discharged to a permanent home prior to their 18th        **;
** birthday?  A child is considered discharged to a permanent home if the discharge    **;
** reason is adoption, guardianship, reunification or lives with relatives.            **;
**                                                                                     **;
** Adapted for new CFSR measures by Terry Shaw                                         **;
**                                                                                     **;
**  Revision History                                                                   **:
**  2007.09.20 Use state id to identify county for non agency type 34 cases            **;
**  2008.08.15 County assignment change - add referral assigned county                 **;
**  2008.10.30 match AFCARS program which excludes cases reentering in target period   **;
**             when table measure is created                                           **;
** 2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group                  **;
** 2015.01.27 and earlier various modifications for CFSR3 measures P2 and P3           **;
** 2016.04.19 modified to identify Los Angeles offices                                 **;
** 2022.06.28 modifiec to use RSP_CNTY when no other county ID available j magruder    **;
**                                                                                     **;
*****************************************************************************************;
***
Individual Measure C3.1:  Of all children in foster care for 24 months or longer on the first day of the 
12-month target period, what percent were discharged to a permanent home by the last day of the 12-month 
period and prior to their 18th birthday?  

A child is considered as discharged to a permanent home if the discharge reason reported to AFCARS is 
reunification, live with relative, guardianship, or adoption.

The denominator for this measure includes children who meet the following criterion:
- The child is in foster care for 24 continuous months or longer on the first day of the 12-month 
  target period. 

The numerator for this measure includes children who meet the criterion for the denominator and also meet ALL 
of the following criteria:
- The child's date of discharge from foster care occurs during the 12-month target period, AND
- The child's reason for discharge is reunification, live with relative, guardianship, or adoption, AND
- The child's date of discharge from foster care occurs before the child's 18th birthday.  

***;

data initial_dataset;
   set dwh.ucb_fc_afcars(rename=(ethnic=eth gender_cd=gender));

   if agy_rspc in (35, 6133, 6134) then delete;
   if BIRTH_DT > PE_S_DT then age = 99;

   *** Only interested in children in care for 24 months or longer ***;
   if pe_e_dt ne . then tpe_e_dt=pe_e_dt;
   else tpe_e_dt=&eox. ;

   tic = tpe_e_dt - pe_s_dt;
   *if tic lt 730 then delete;

   if oh_e_dt ne . then sort_dt=oh_e_dt;
   else sort_dt=&eox.;

/*
   if cnty_rem ne ' ' then cnty = input(cnty_rem, best2.);
   if agy_rspc ne 34 and cnty in (.,0,59,98,99) then cnty=input(ucb_cnty_rem,best2.) ;
   if not (1 le cnty le 58) then cnty=98;
*/
   
   * agency responsible type *;
   agency=.;
   if agy_rspc = 34 then agency=1;
   else if agy_rspc=33 or agy_rspc=5603 then agency=2;
   else agency=3;

  select(PLC_FCLC);
      when('2222') FED = 1;            /* Pre-Adopt */
      when('1421','1422') FED = 2;     /* Kin */
      when('1415','1416') FED = 3;     /* Foster */
      when('1414', '2200') FED = 4  ;  /* FFA */
      when('1419') FED = 9;            /* Court Specified Home */
      when('7208') FED = 9.2;          /* Tribally Approved Home */
      when('1417') FED = 10;           /* Group */
      when('1418','7027') FED = 11;    /* Shelter */
      when('5411') FED = 14;           /* Guardian */
      when(' ','0','.') FED = 99;      /* Missing */
      otherwise FED = 29;              /* Other */
   end;

   select(scp_rltc);
      when('1636') scpr=1;
      when('1637') scpr=2;
      when('1638') scpr=3;
      when('1639') scpr=4;
      when('6715') scpr=5;              /* Self */
      when('6990') scpr=6;              /* NREFM Guardian */
      when('6991') scpr=7;              /* NREFM Nonguardian */

      otherwise scpr = 99;              /* Missing */
   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;

   *** Termination reason type ***;
   ***
   	5426	0030	01			Adoption Finalized
	5434	0013	09			Guardianship
	5438	0038	13			Private Adoption Agency (Non-CWS)
	5439	0036	14			Reunified with Parent/Guardian (Court)
	5440	0037	15			Reunified with Parent/Guardian (Non-Crt)
	5505	0016				Adoption Finalized
	5513	0009				Child Released Home
	5516	0012				Guardianship
	5519	0015				Private Adoption Agency (non-CWS)
   ***;
   if TERM_TY_C in (5439, 5440, 5513,
                    5426, 5438, 5505, 5519,
                    5434, 5516, 6530, 6531
                    ) then permen = 1; 


/*Add ICWA and Tribal status codes 2024.10.14 j magruder*/
select (ICWA_ELGCD) ;
   when ("Y") ICWA = 1;  /*ICWA Eligible*/
   when ("N") ICWA = 2; /*ICWA Not Eligible*/
   when ("P") ICWA = 3;  /*ICWA Status Pending*/
   otherwise ICWA = 99; /*ICWA Not asked, unknown, mssing*/
end;

select (INDN_STC) ;
   when ("1212") TRIBAL_STC = 1; /*Tribal Member of at least one tribe*/
   when ("1211") TRIBAL_STC = 2; /*Eligible for Tribal Membership but not member of any tribe*/
   when ("1214") TRIBAL_STC = 3; /*Neither of above but Pending Verification*/
   when ("1210") TRIBAL_STC = 4; /*None of above but Claims Tribal membership*/
   when ("1213") TRIBAL_STC = 5; /*none of above and 1+ tribes found ineligible for membership*/
   when ("6532") TRIBAL_STC = 6; /*None of above and no tribe has responded after 60 days*/
   otherwise TRIBAL_STC = 99;     /*No Tribal Membership data available*/
end;


run;

proc sort data = initial_dataset;  by fkclient_t pe_s_dt tpe_e_dt oh_s_dt SORT_DT; run;


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

%let empty=0;

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

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

data _null_;


   %if &sq. ^= 1 & &year.=&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 & &year.=&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 qtr = &stqtr. %to &enqtr. ;

***** Organize the date fields *****;

data _null_; 

   length sm $5 ;

   if &qtr=1 then sm='01JAN';
   if &qtr=2 then sm='01APR';
   if &qtr=3 then sm='01JUL';
   if &qtr=4 then sm='01OCT';

   sdate=compress("'"||sm||&year.||"'d");
   edate=compress("'"||sm||%eval(&year.+1)||"'d");

   call symput('sdate',sdate);
   call symput('edate',edate);
   output; 

run;

** The denominator consists of children exiting to reunification
   in the 12 months immediately preceding the specific time period ***;

data episodes;
   set initial_dataset;
   by fkclient_t;

   where pe_s_dt le &sdate. le tpe_e_dt ;
   if oh_s_dt gt &sdate. and not first.fkclient_t then delete;
run;

proc sort data=episodes; by fkclient_t pe_s_dt tpe_e_dt oh_s_dt; run;

data base(rename=(fed=p_plc scpr=p_scpr)) /* too_short(keep=fkclient_t pe_s_dt ptic tic);*/ ;
   set episodes;
   by fkclient_t pe_s_dt ;

   *** This is age at the start of the 12 month period ***;

   if age ne 99 then do;

      if birth_dt ne . then AGE = int((intck('month',BIRTH_DT,&sdate.)-(day(&sdate.) lt day(BIRTH_DT)))/12); 
      else age=.;
   end;

   *** This is age at exit ***;
   if birth_dt ne . and pe_e_dt ne . then exit_age = int((intck('month',BIRTH_DT,pe_e_dt)-(day(pe_e_dt) lt day(BIRTH_DT)))/12);
   else exit_age=.;

   *** The measure is looking for exits prior to age 18 ***;
   if not (0 le AGE le 20) then age=99;

   if not (0 le exit_age le 20) then age_exit=99;

   if BIRTH_DT = . then age = 99;

   *** The los has to be 24 months or longer at start of period ***;
        if int((intck('month',pe_s_dt,&sdate.) - (day(&sdate.) lt day(pe_s_dt)))/12) < 1  then PTIC_FLAG = 1;
   else if int((intck('month',pe_s_dt,&sdate.) - (day(&sdate.) lt day(pe_s_dt)))/12) < 2  then PTIC_FLAG = 2;
   else if int((intck('month',pe_s_dt,&sdate.) - (day(&sdate.) lt day(pe_s_dt)))/12) >= 2 then PTIC_FLAG = 3;


run;

proc sort data=base; by fkclient_t pe_s_dt; run;

*add to eliminate persons over age 18 on first day of period from denominator
 (and numerator) jjm 2007.09.20);
data base1;
        set base;
   if age < 18 and age ge 0;
   run;

data uniq_base;
   set base1;
   by fkclient_t;
   if last.fkclient_t;
run;


***** Bring in the assignment county *****;
*** Get the case and referral county assignment at the period start date ***;

proc sql;

   create table county_at_sdate as
   select A.FKCLIENT_T,
          A.plcep_id,
		  a.agency,
		  a.cnty_rem,
          B.CNTY_SPFCD as case_cnty,
          B.office_s_DT,
		  B.office_e_dt,
		  b.cws_off_no,
          B.FKCWS_OFFT,
		  b.type
   from uniq_base A left join dwh.UCB_OFFICE1 B
   on A.FKCLIENT_T = B.FKCHLD_CLT and
         B.office_s_DT ne  . and
         B.office_s_DT <= &sdate <= B.office_e_dt
   order by FKCLIENT_T, B.office_s_DT, B.office_e_DT
   ;
quit;

/*if no assignment from office assignment table, use state id table*/


/*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 county_at_sdate_02a county_at_sdate_02b 
		(drop = case_cnty office_s_dt office_e_dt type);
	set county_at_sdate;
	if office_s_dt ne . then output county_at_sdate_02a;
		else output county_at_sdate_02b;
	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 county_at_sdate_02b1 as
		select st_id.cnty_spfcd as case_cnty,
			st_id.office_s_dt,
			st_id.office_e_dt,
			county_at_sdate_02b.*
			from county_at_sdate_02b A left join dwh.st_id B
			on A.FKCLIENT_T eq B.fkchld_clt and
				B.office_s_Dt <= &sdate < B.office_e_dt
			order by A.FKCLIENT_T, B.office_s_dt, B.office_e_dt
		;
	quit;

/*if all else fails, use county of removal*/
data county_at_sdate_02b2;
	set  county_at_sdate_02b1;
	if case_cnty = "  " then case_cnty = cnty_rem;
	run;

/*Put two files back together - i.e., add state ID county derived identifiers
  to those records in entries_02 that are lacking cnty_spfcd, office_s_dt and
  office_e_dt data*/
data county_at_sdate_03;
	set county_at_sdate_02a county_at_sdate_02b2;
	run;

/*proc sort data = county_at_sdate; by FKCLIENT_T office_s_DT office_e_dt; run;*/

*** Where more than 1 county_at_sdate, use the most recent. ***;

data case_cnty ;
   set county_at_sdate ;
   by FKCLIENT_T ;
   if last.FKCLIENT_T then output ;
   *drop START_DT tEND_DT END_TM;

run;
/*
proc tabulate data = case_cnty missing format = 7.0;
	class type agency;
	table type all, agency all;
	run;

proc contents; run;
*/
*** Join the county at end file with the original file ***;


proc sql;

   create table cnty_1 as
   select a.*,
          b.FKCWS_OFFT,
          b.case_cnty,
		  b.type
   from uniq_base as a left join case_cnty as b
   on a.FKCLIENT_T = b.FKCLIENT_T
	order by fkclient_t;
   quit;

data uniq_base1a(drop=CASE_CNTY NCASE_CNTY);
set cnty_1;

NCASE_CNTY = input(CASE_CNTY, best2.);
if 1 <= NCASE_CNTY <= 58 then CNTY = NCASE_CNTY;
*else CNTY = 98;
else CNTY = rsp_cnty;
if CNTY > "58" then CNTY = "98";
/*2022.06.28 j magruder to use rsp_cnty when no other county ID available*/
run;
/*
proc tabulate data = uniq_base1a missing format = 7.0 noseps;
	class cnty agency;
	table cnty all, agency all;
run;
proc print data = uniq_base1a (Obs = 20);
	where fkclient_t = "03MRKnOGSW";
		run;
*/
/*get office number and name*/

proc sql;
    create table uniq_base1b as
        select uniq_base1a.*,
            cws_offt.cws_off_no,
            cws_offt.cws_off_nm
        from uniq_base1a left join cws.cws_offt
        on uniq_base1a.fkcws_offt = cws_offt.identifier;
    *order by IDENTIFIER, START_DT, tEND_DT, END_TM;
    quit;
quit;

data uniq_base1;
        set uniq_base1b;
        if CNTY = 19 then do;
                     if cws_off_no in("S3253") then COUNTY = 1901; /*Belvedere*/
                else if cws_off_no in("S1277") then COUNTY = 1902; /*Compton-Carson*/
                else if cws_off_no in("S1280") then COUNTY = 1903; /*El Monte*/
                else if cws_off_no in("S1254") then COUNTY = 1904; /*Glendora*/

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

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

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

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

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

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

                else if cws_off_no in("S1286") then COUNTY = 1943; /*Juvenile Court Services*/
                else if cws_off_no in("S0246", "S0247", "S9056", 
                             "S4261", "S0394") then COUNTY = 1945; /*Administration*/
                else if cws_off_no in("S0299") then COUNTY = 1950; /*Probation*/
                else if cws_off_no in("S9072") then COUNTY = 1999; /* assign to missing *//*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 sort data=uniq_base1; by fkclient_t pe_s_dt tpe_e_dt; run;

*data measure_01;
data measure; *** eliminate adjustment for exits and reentries in same period, see below**;
   set uniq_base1;

   if permen=1 then do;                      *** Exit to permanency ***;
      
      if pe_e_dt ne . and 
         pe_e_dt lt &edate. then do;         *** Exit within a year of start ***;

	     if exit_age lt 18 then count=1;

	  end;

   end;

      if count = 1 then do;
                if term_ty_c in (5439 5440 5513) then ptype = 1;                     /*reunification*/
                else if term_ty_c in (5426 5438 5505 5519 6530 6531) then ptype = 2; /*adoption*/
                else if term_ty_c in (5434 5516) then ptype = 3;                     /*guardianship*/
                else ptype = 4;
        end;


   if count ne 1 then do;
                   count = 0;

                      if pe_e_dt ne . and pe_e_dt lt &edate. then ptype = 4 ;
                      else ptype = 5;

   end;

run;



***** Need to run once for the state and once for the counties *****;

%macro summs(lvl);

proc summary data=measure nway;
   class 
   %if &lvl=cnty %then %do;
      cnty 
      COUNTY
   %end;
     agency age ethnic gender_cd p_plc p_scpr ptype PTIC_FLAG ICWA TRIBAL_STC
      ;
   var count;
   output out=measure_summ sum=;
run;

*** We want to have a total for AGENCY as well, so there needs to be a
    second series of summaries excluding AGENCY
***;

proc summary data=measure nway;
   class 
   %if &lvl=cnty %then %do;
      cnty COUNTY
   %end;
      age ethnic gender_cd p_plc p_scpr ptype PTIC_FLAG  ICWA TRIBAL_STC
      ;
   var count;
   output out=ameasure_summ sum=;
run;

proc sort data=measure_summ(rename=(_freq_=denom  )); 
   by 
   %if &lvl=cnty %then %do;
      cnty COUNTY
   %end;
       agency age ethnic gender_cd p_plc p_scpr ptype PTIC_FLAG  ICWA TRIBAL_STC;
run;

proc sort data=ameasure_summ(rename=(_freq_=denom  )); 
   by 
   %if &lvl=cnty %then %do;
      cnty COUNTY
   %end;
       age ethnic gender_cd p_plc p_scpr ptype PTIC_FLAG ICWA TRIBAL_STC;
run;

data column_&lvl.;
   set measure_summ
       ameasure_summ (in=a);
  
   %if &lvl=state %then %do;
      cnty = 0;
      COUNTY = 0000;
   %end;

   if a then agency=4 ;

run;

proc sort data=column_&lvl.; 
 by cnty COUNTY agency age ethnic gender_cd p_plc p_scpr ptype PTIC_FLAG ICWA TRIBAL_STC;
run;

%mend summs;

%summs(state);
%summs(cnty);

data columns;
   set column_state
       column_cnty
   ;

   period_dt=yyq(&year.,&qtr.) ;

   drop _type_ ;
run;

%if &empty.=0 %then %do;

data _null_;
   set sashelp.vtable(keep=libname memname);
   *where upcase(libname)="LOCAL" ; 
   if upcase(memname)="P2_LA1" then 
      call symput('empty',1);
run;

%end;

data p2_la1;
   set 
       %if &empty.=1 %then %do; p2_la1 %end;
	   columns
	   ;

   %if &empty.=1 %then %do;
   label agency='Agency: CW,Prob.,Other,Total'
         age='Age at start of period'
		 ethnic='Childs ethnicity'
		 gender_cd='Childs gender'
		 period_dt='The start of the period'
		 cnty='County Responsible'
		 COUNTY='Office Responsible'
		 count='Exit within 12 months and <18yrs'
		 denom='Count of children'
		 p_plc='Positional placement type (start of period)'
		 p_scpr='Last placement type (initial entry)'
                 ptype='Exit type'
                 PTIC_FLAG='Time in care flag'
		 ICWA='ICWA Status'
                 TRIBAL_STC="Indian Tribal Status"

         ;
   %end;

run;

%end;

%end;

%mend p2;

%p2(1,1998,4,2024);

proc sql;
create table p2_la2 as
select AGENCY, PERIOD_DT, ETHNIC, PTIC_FLAG, AGE, GENDER_CD, P_PLC, P_SCPR, PTYPE, ICWA length=4, TRIBAL_STC length=4, sum(COUNT) as COUNT, sum(denom) as denom
from p2_la1
where CNTY = 19
group by AGENCY, PERIOD_DT, ETHNIC, PTIC_FLAG, AGE, GENDER_CD, P_PLC, P_SCPR, PTYPE, ICWA, TRIBAL_STC;
quit;

data p2_la3;
set p2_la2;

COUNTY = 1900;
CNTY = 19;

run;

data p2_la4;
set p2_la1 p2_la3;

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

run;

data dvlp.p2;
set p2_la4;

length AGE COUNT DENOM AGENCY PERIOD_DT CNTY COUNTY ETHNIC /* P_PLC */ P_SCPR GENDER_CD PTYPE PTIC_FLAG 4.;

run;

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

proc sql;

create index IDX on dvlp.p2(AGENCY, PTIC_FLAG, CNTY, PERIOD_DT);

quit;

data test.p2;
set dvlp.p2;
run;

proc sql;

create index IDX on test.p2(AGENCY, PTIC_FLAG, CNTY, PERIOD_DT);

quit;