**************************************************************************;
**                                                                       **;
** D:\Siblings\SIBLING_VARIABLE_CREATION_2006Q3                          **;
**                                                                       **;
** Copyright (c) 2006 Center for Social Services Research,               **;
** University of California at Berkeley. All rights reserved.            **;
** Center for Social Services Research - Univ of California at Berkeley  **;
** CWS/CMS Reporting:  Abuse in Foster Care                              **;
**                                                                       **;
** This program uses the UCB_FC dataset and the Client Relationship      **;
** tables to produce a dataset containing a sibling identifier and a     **;
** sibling count.                                                        **;
**                                                                       **;
** FOR:  Barbara Needell          BY: Terry Shaw                         **;
**                                                                       **;
** The following dataset is created:                                     **;
**     siblings_09q2                                                     **;
**                                                                       **;
***************************************************************************;

%let quarter=Q4_2024;
%let tframe=24q4;

libname library "/wss1/SAS/SASDATA/CWS_CMS/&quarter./SOURCE";
libname cws     "/wss1/SAS/SASDATA/CWS_CMS/&quarter./SOURCE";
libname cms     "/wss1/SAS/SASDATA/CWS_CMS/&quarter./SOURCE";
libname ca      "/ssa4/SAS/SASDATA/CWS_CMS/&quarter./";
libname elf     "/ssa4/SAS/SASDATA/CWS_CMS/&quarter./";
libname dwh     "/ssa4/SAS/SASDATA/CWS_CMS/&quarter./";
*libname events  "/dss/temp";
*libname terry   '/dss/TSHAW';
*libname temp    "/dss/temp";
libname temp     "/pool01/TEMP/CWS_CMS/SLEE/DATA";

libname userstor "!sasroot/ud_templates" ;

***** The dataset supplied_data contains the data to be used as a   *****;
***** basis for the creation of a family counter                    *****;

proc sort data=elf.ucb_fc_afcars out=ucb_fc; by fkclient_t; run;

***** Only need to keep the client key at this point *****;

proc sort data= ucb_fc(keep= fkclient_t) nodupkey out= supplied_data(rename=(fkclient_t=fkclient)) ;
   by fkclient_t ;
run ;

*** First link to fkcli_t in cws.clnre.
*** FKCLIENT_T - Mandatory Foreign Key that INCLUDES_A_PRIMARY_INDIVDL_AS a CLIENT. ;

data clnre ;
   set cws.cln_relt(keep= fkclient_t fkclient_0 clntrelc) ;
   if clntrelc in (179,180,181,182,183,184,276,277,278,279,280,281) ;
   *** 181, 184, 278, and 281 are for Step relationships.
   *** As per Barbaras instructions on 10/03/01, include them. ;
   fkclient = fkclient_t ;
run ;

proc sort data= clnre ;    by fkclient ;    run ;

data MatchFirstOnFkcli_t ;
   merge supplied_data(in=a) clnre(in=b) ;
   by fkclient ;
   if a and b ;
   *** When fkclient in the supplied_data links to fkcli_t in clnre, the value in
   *** fkcli_0 will identify the family. ;
   family = fkclient_0 ;
run ;

*** Want to eliminate records with duplicate values of the fkclient family combination.
*** These would indicate the same client-family relationship.  Moreover, if this is
*** is not done, there will be problems in later merges with duplicate values of
*** the by-variable in more than one data set. ;

proc sort data= MatchFirstOnFkcli_t nodupkey ;    by fkclient family ;    run ;

*** Now link to fkcli_0 in cws.clnre.
*** FKCLIENT_0 - Mandatory Foreign Key that INCLUDES_A_Secondary_INDIVDL_AS a CLIENT. ;

data clnre ;
   set clnre ;
   fkclient = fkclient_0 ;
run ;

proc sort data= clnre ;   by fkclient ;    run ;

data MatchSecondOnFkcli_0 ;
   merge supplied_data(in=a) clnre(in=b) ;
   by fkclient ;
   if a and b ;
   *** When fkclient in supplied_data links to fkcli_0 in clnre, the value in *****;
   *** fkcli_t will identify the family. *****;
   family = fkclient_t ;
run ;

*** Want to eliminate records with duplicate values of the fkclient family combination.
*** These would indicate the same client-family relationship.  Moreover, if this is
*** is not done, there will be problems in later merges with duplicate values of
*** the by-variable in more than one data set. ;

proc sort data= MatchSecondOnFkcli_0 nodupkey ;   by fkclient family ;   run ;

*** Now merge MatchFirstOnFkcli_t and MatchSecondOnFkcli_0. Both are sorted by
*** fkclient family.  If there are duplicate values of the same fkclient family
*** combination in both of the data sets being merged, only one record
*** will result for a given fkclient family combination. ;

*** NOTE-  Earlier test showed that if the data sets MatchFirstOnFkcli_t and
*** MatchSecondOnFkcli_0 are concatenated, rather than merged, with duplicate values
*** of the same fkclient family combination being eliminated after concatenation,
*** the results are identical to those obtained from merging. ;

data Match ;
   merge MatchFirstOnFkcli_t(in=a) MatchSecondOnFkcli_0(in=b) /* MatchMaternal(in=c) */ ;
   by fkclient family;
   length tmp1 $10;
   output;
   tmp1=fkclient;
   fkclient=family;
   family=tmp1;
   output;
run ;

proc sort data=match nodupkey ; by fkclient family;

data partone;
   set match;
   retain tempfam temp2fam;
   length tempfam temp2fam $10;
   by fkclient family ;
   if first.fkclient then do;
      tempfam=family;
	  temp2fam=' ';
      output;
   end;
   else if tempfam ne family then do;
      output;
	  temp2fam=family;
	  family=tempfam;
	  output;
	  family=temp2fam;
	  fkclient=tempfam;
   end;
run;

proc sort data=partone nodupkey; by family fkclient;

data parttwo;
   set partone;
   retain tempfk temp2fk;
   length tempfk temp2fk $10;
   by family fkclient;
   if first.family then do;
      tempfk=fkclient;
	  temp2fk=' ';
      output;
   end;
   else if tempfk ne fkclient then do;
      output;
	  temp2fk=fkclient;
	  fkclient=tempfk;
	  output;
	  fkclient=temp2fk;
	  family=tempfk;
	  output;
   end;
run;

proc sort data=parttwo nodupkey; by fkclient family; run;

data partthr;
   set parttwo;
   length tmp $10;
   output;
   tmp=fkclient;
   fkclient=family;
   family=tmp;
   output;
run;

proc sort data=partthr nodupkey out=match3; by fkclient family; run;

***** Match contains all of the sibling pairs for a FKCLIENT *****;

*** Earlier inspection of results shows that not all fkclients in the point-in-time
*** caseload data set link to the Client Relationship Table.
*** Get a count (for the log) of how many unique fkclients there are in Match
*** after completion of the two merges of the point-in-time caseload dataset
*** with the Client Relationship table. ;

data Match_fkclients ;
   set Match3(keep= fkclient) ;
   by fkclient ;
   if first.fkclient ;
run ;

*** Need to capture these fkclients which did not link to the Client Relationship
*** Table and put them into a data set of fkclients which didnt link. ;

data NoMatch1 ;
   merge supplied_data(in=a) match_fkclients(in=b) ;
   by fkclient ;
   if a and not b ;
   keep fkclient ;
run ;

*** Earlier test results showed that there are a small number of records (5-10)
*** in Match for which  fkclient = family.  Since we are going to create records
*** like these later for each fkclient so that the fkclient is grouped together
*** with his or her familys,  they must be deleted if they already exist. ;

data oldmatch; set match3; run;

data Match(keep= fkclient family clntrelc)
     fkclient_eq_family(keep= fkclient family fkclient_t fkclient_0 clntrelc) ;
   set oldMatch ;
   if         fkclient ne family then output Match ;
      else if fkclient =  family then output fkclient_eq_family;
run ;

proc sort data=match; by fkclient family;

*** Need to find out if values of fkclient were lost from Match. This would occur if
*** a record for which  fkclient = family was the only record for a given fkclient
*** in Match.  Since effectively these fkclients didnt link to a family, they
*** should be put in a dataset of fkclients which didnt link to the Client Relationship
*** Table.  Create a dataset with only the resulting unique fkclients in Match
*** so that this information can be read from the log. ;

data Match_fkclients_ne_family ;
   set Match(keep= fkclient) ;
   by fkclient ;
   if first.fkclient ;
run ;

*** Want to capture the fkclients which may have been lost when records were deleted
*** for which fkclient = family ;

data NoMatch2 ;
   merge Match_fkclients_ne_family(in=a keep= fkclient)
         Match_fkclients(in=b) ;
   by fkclient ;
   if b and not a ;
   keep fkclient ;
run ;

*** Want to re-name the most recent count of unique fkclients as Match_fkclients ;

data Match_fkclients; set Match_fkclients_ne_family;   run ;

*** Inspection of earlier results indicates that
*** the two merges between the point-in-time caseload data set and the Client
*** Relationship table, which created the data set Match, picked up values of family which
*** are not in  the point-in-time caseload data set.   In other words, these are
*** familys who are not currently in the system.  These records need to be deleted. ;

*** Match is sorted by fkclient.  Sort it by family ;

proc sort data= Match ;     by family ;     run ;

data Match ;
   merge supplied_data(in=a rename=(fkclient= family))  Match(in=b) ;
   by family ;
   if a and b ;
run ;

*** Inspection of earlier results indicates that when records for familys
*** no longer in the system are deleted, some fkclients are dropped from Match.
*** These fkclients, who are in the point-in-time data set but who did not
*** link to a family in the point-in-time data set, should be put into the
*** NoMatch data set.

*** Match is sorted by family.  Sort it by fkclient ;

proc sort data= Match ;    by fkclient ;    run ;

data Match_fkclients ;
   set Match(keep= fkclient) ;
   by fkclient ;
   if first.fkclient ;
run ;

*** Now get from the supplied_data the fkclients which were lost when records were
*** deleted for familys not in the system at the point-in-time.  If these fkclients are
*** already in NoMatch1 or NoMatch2, do not output them to NoMatch3. ;

data NoMatch3 ;
   merge supplied_data(in=a) match_fkclients(in=b) NoMatch1(in=c) NoMatch2(in=d) ;
   by fkclient ;
   if a and not b and not c and not d ;
   keep fkclient;
run ;

*** Now interleave by fkclients the three data sets containing fkclients
*** which didnt match to clnre, the Client Relationship Table. ;

data NoMatch ;
   set NoMatch1 NoMatch2 NoMatch3 ;
   by fkclient ;
run ;

***** One record per fkclient *****;

data nomatch;
   set nomatch;
   by fkclient;
   if first.fkclient then output;
run;

*** Verify that there is only one record per fkclient in NoMatch.
*** NoMatch_check should have no records. ;

data NoMatch_check ;
   set NoMatch(keep= fkclient) ;
   by fkclient ;
   if not (first.fkclient and last.fkclient) ;
run ;

*** Make sure that there are not records in Match for fkclients which are also in
*** NoMatch.   Check that the number of unique fkclients in Match after the
*** following data step is the same as the number of fkclients in Match_fkclients
*** (created 4 data steps prior) ;

data Match ;
   merge Match(in=a) NoMatch(in=b) ;
   by fkclient ;
   if a and not b ;
run ;

*** Get a count for the job log of the number of unique fkclients in Match after
*** the preceding data step. ;

data Match_fkclients ;
   set Match(keep= fkclient) ;
   by fkclient ;
   if first.fkclient ;
run ;

***      Earlier in this program, we checked that links between clients in the
*** point-in-time caseload data set and their familys in the Client Relationship Table
*** picked up familys who are themselves in the point-in-time caseload data set.
*** Later we identified and put into the NoMatch data set fkclients in the point-in-time
*** caseload dataset which effectively did not link to the Client Relationship Table.
***      Now we want a second check that we do not have familys in Match which also
*** appear as fkclients in MoMatch.  This could have occurred due perhaps to some
*** inconsistency in the data. ;

*** Match is sorted by fkclient.  Sort it by family ;

proc sort data= Match ;    by family ;    run ;

data Match  NoMatch4 ;
   merge Match(in=a) NoMatch(in=b rename=(fkclient=family)) ;
   by family ;
   if         a and not b  then output Match ;
      else if a and b      then output NoMatch4 ;
run ;

proc sort data=nomatch4; by fkclient; run;

*** Now interleave by fkclient NoMatch4 with NoMatch. ;

data NoMatch ;
   set NoMatch NoMatch4 ;
   by fkclient ;
   length family $10 clntrelc 3 ;
   *** Since fkclients in Match have no familys in the system at the point-in-time,
   *** put their unique fkclient value in the variable family.  This variable can
   *** serve as the famid (family group identifier, i.e., a family group of 1).
   *** In a subsequent program rename this variable as famid. ;
   family = fkclient ;
   clntrelc = 0998 ;
run ;

*** Match is sorted by family.   Sort it by fkclient. ;

proc sort data= Match ;    by fkclient ;    run ;

*** Need to add to Match a record for the fkclient who is linked to familys so
*** that this fkclient will also be picked up as part of the family group. ;

data Match  ;
   set Match ;
   by fkclient ;
   output ;
   if last.fkclient then do ;
      family = fkclient ;
          clntrelc = 0999 ;
          output ;
   end ;
run ;

*** Verify that there are at least two records per fkclient in Match.
*** Match_check should have no records. ;

data Match_check ;
   set Match(keep= fkclient) ;
   by fkclient ;
   if first.fkclient and last.fkclient ;
run ;

*** Interleave Match and NoMatch.  Both datasets are sorted by fkclient. ;

data final_ds ;
   *** NOTE- SUPPLIED_DATA familys_full_DS has 24 characters. (DS = dataset) ;
   set Match NoMatch ;
   by fkclient ;
run ;

proc contents data= final_ds ;
   title "final_ds  - created by familys1P" ;
run ;

proc format;
   value fam
    0179= '179 BR Brother/Brother'
    0180= '180 BR Brother/Brother (Half)'
    0181= '181 BR Brother/Brother (Step)'
    0182= '182 BR Brother/Sister'
    0183= '183 BR Brother/Sister (Half)'
    0184= '184 BR Brother/Sister (Step)'
    0276= '276 SI Sister/Brother'
    0277= '277 SI Sister/Brother (Half)'
    0278= '278 SI Sister/Brother (Step)'
    0279= '279 SI Sister/Sister'
    0280= '280 SI Sister/Sister (Half)'
    0281= '281 SI Sister/Sister (Step)'
    0998= '998 Did Not Link'
    0999= '999 Self/Self Link Created' ;
run;

*** Get a count of the kinds of family relationship. ;

proc freq data= final_ds  ;
   tables clntrelc ;
   format clntrelc fam. ;
   title1 "final_ds" ;
   title2 'First merge by fkcli_t, second merge by fkcli_0' ;
run ;

*** FUNCTION- Inputs a dataset created in a previous program which
*** contains records for clients in the point-in-time caseload data set and their links
*** to familys in the Client Relationship Table(clnre).  The family groupings in this
*** incoming data set also have a record for the client in the point-in-time caseload
*** data set who has the links to the familys in the Client Relationship Table.
***      This program (familys2P) identifies and obtains a family identifier (famid)
*** which links the familys in a family group.  This famid is attached (via the
*** variable fkclient) to all clients in the point-in-time caseload data set.  This famid
*** is then used to calculate a fam_count which is also attached to all clients in a
*** renamed version of the point-in-time caseload data set (via the variable famid).
*** For example, if there were three familys in the renamed point-in-time caseload
*** data set with the same famid ABCDE12345, all would also have the value of three
*** for fam_count. ;

*** SUPPLIED_DATA familys_full_DS (DS = dataset) is sorted by fkclient. ;
*** Need to treat records in wj.familys_full_dataset which didnt link to the Client
***     Relationship Table differently than those which did.
***    Fkclients which didnt match have only record per fkclient while
*** those which did match have at least two records per fkclient.
*** SUPPLIED_DATA familys_full_DS  has three variables- fkclient family cntlrelc        ;

data Match NoMatch ;
   set final_ds (keep= fkclient family) ;
   by fkclient ;
   if not    (first.fkclient and last.fkclient) then output Match ;
      else if first.fkclient and last.fkclient  then output NoMatch ;
run ;

*** Since fkclients in NoMatch have no familys in the system at the point-in-time
*** caseload data set, their unique fkclient value was put in the variable family
*** in the program family1P.  Since this variable is going to serve as the famid
*** (family group identifier, i.e., a family group of 1), rename family as famid. ;

proc datasets nolist ;
   modify Nomatch ;
  rename  family = famid ;
run ;

*** Get a count (for the job log) of unique fkclients in Match ;

proc sort data=match; by fkclient family;

data Match_fkclients ;
   set Match(keep= fkclient) ;
   by fkclient  ;
   if first.fkclient ;
run ;

*** Get a count (for the job log) of unique fkclients in NoMatch ;

data NoMatch_fkclients ;
   set NoMatch(keep= fkclient) ;
   by fkclient  ;
   if first.fkclient ;
run ;

***      Need to add a counter to Match which identifies all familys associated
*** with a given fkclient. Suppose a family group has three familys, including the
*** the first fkclient with which the familys are associated.   This grouping
*** of three familys will occur three times in the data set Match, once each
*** time one of the familys appears as the fkclient with the other familys
*** grouped along with this fkclient.
***      When this process is automated, a different family group identifier (famid)
*** will be assigned to the group each time it occurs.  In this example, three
*** famids will be assigned.   Since we only need and want one famid per family
*** group, we will keep the famid assigned the first time the family group appears.
*** Since the incoming data set (from the previous program) is sorted by (ascending)
*** fkclient, this group will appear for the first time associated with the family
*** with the lowest alpha-numeric value of fkclient.
***      Since this unique alpha-numeric value of fkclient links the familys in the
*** group, use it as the famid for the family group.  The variable count will enable
*** us to identify the first occurrence of the grouping and to retain this unique
*** value of fkclient as the famid. ;

data Match  ;
   set Match ;
   by fkclient ;
   if first.fkclient then count + 1 ;
   *** Rename fkclient as famid to indicate its future role. ;
   rename fkclient = famid ;
run ;

proc sort data= Match ;   by family count ;     run ;

data Match ;
   set Match ;
   by family ;
   if first.family ;
   *** Since Match is sorted by (ascending) count within family, the invocation of
   *** "if first.family" will capture the first famid assigned to the family. ;
   *** Since family is the variable which will be used to link to fkclient in
   *** point-in-time caseload data set in order to attach the famid to fkclient,
   *** rename family as fkclient to indicate its future role. ;
   rename family = fkclient  ;
   drop count ;
run ;

*** Get a count of how many records in Match have fkclient = famid. ;

data check_fkclient_eq_famid ;
  set Match(keep= fkclient famid) ;
  if fkclient = famid ;
run ;

*** Interleave the two data sets which contain the famids which will be attached to
*** the point-in-time caseload data set. ;

data famid ;
   set Match NoMatch ;
   by fkclient ;
run ;

***** Check for incorrect links *****;

proc sort data=famid; by fkclient famid; run;
proc sort data=famid nodupkey; by fkclient;

*** Use the macro variable  SUPPLIED_DATA  to bring in the current point-in-time
*** caseload data set.  However, minimize the use of macro variables since numerous
*** invocations and subsequent resolutions of Symbolgen make the job log hard to read.
*** Use them in titles, however, since they make the output more useful. ;

proc sort data= ucb_fc(keep= fkclient_t ) nodupkey out= supplied_data(rename=(fkclient_t=fkclient)) ;
   by fkclient_t ;
run ;

*** Attach the variable famid to the unique fkclients  in the supplied_data.
*** All clients in the point-in-time caseload data set should pick up a famid.  If they
*** do not, put them in the Nofamid data set. Is things are working properly,
*** Nofamid should have zero observations. ;

data supplied_data_famid  supplied_data_Nofamid ;
   merge supplied_data(in=a) famid(in=b) ;
   by fkclient ;
   if a & b ;
   NumVar = 1 ;
   if         famid ne ' ' then output supplied_data_famid  ;
      else if famid =  ' ' then output supplied_data_Nofamid ;
run ;

proc means data= supplied_data_famid  nway noprint ;
   class famid ;
   var NumVar ;
   output out= fam_count(drop= _freq_ _type_) sum(NumVar)=fam_count ;
run ;

***** proc freq data= fam_count ;
*****   tables fam_count ;
*****   title "after Proc Means on  SUPPLIED_DATA, famid by famid to get famcount" ;
***** run ;

*** Attach variable famcount to the unique fkclients in supplied_data_famid ;

proc sort data= supplied_data_famid ;   by famid fkclient ;     run ;

data temp.siblings_&tframe. ;
   merge fam_count(in=a) supplied_data_famid(in=b) ;
   by famid ;
   if a & b ;
   keep fam_count famid fkclient;
   label famid="Family Identifier-Fkclient format"
         fam_count="Number of siblings in THIS dataset";
run ;

proc contents data=temp.siblings_&tframe.;
   title "Final dataset with Family Count and Family ID";
   *title2 "Original data &dsin , created data &dsout ";
run;

proc freq data= temp.siblings_&tframe. ;
   tables fam_count ;
   title1 "Siblings only " ;
run ;
***************************************************************************;
**                                                                       **;
** SIBLING_PIT_FILES.sas                                                 **;
**                                                                       **;
** Copyright (c) 2006 Center for Social Services Research,               **;
** University of California at Berkeley. All rights reserved.            **;
** Center for Social Services Research - Univ of California at Berkeley  **;
** CWS/CMS Reporting:  Sibling Analysis                                  **;
**                                                                       **;
** This program takes a PIT file - created using a macro embedded in the **;
** program and the Sibling ID file and combines them to specify whether  **;
** siblings are placed together or not.                                  **;
**                                                                       **;
** FOR:  Barbara Needell          BY: Terry Shaw                         **;
**                                                                       **;
** This program was updated on 12/04/2002 to include the new PIT code    **;
**    (changes in ILSP and Limbo kids) and to incorporate the Placement  **;
**    /Ethnicity/and Matrix reports into a single program.               **;
** This program was updated on 9/22/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 10/9/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 9/22/2004 to enclose the program in macro **;
**    code to facilitate running multiple time periods.                  **;
** This program was updated on 3/14/2005 to change the placement home    **;
**    matching process to use IDENTIFIER rather than the created place-  **;
**    home address indicator because of confidentiality.                 **;
** This program was updated on 1/30/2005 to change the macro calling the **;
**    point in time code to use the most recent version of that code.    **;
** This program was updated on 9/26/2006 to update the way that siblings **;
**    are categorized.  Siblings are now calculated at the county level  **;
**    rather than the state level because counties do not 'see' siblings **;
**    outside of their county boundaries.                                **;
**                                                                       **;
** The following files are created:                                      **;
**     SIBf_00_xxx200y_s.xml   (Summary file)                            **;
**     SIBf_P0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_E0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_00_xxx200y_0.xml                                             **;
**                                                                       **;
**  2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group   **;
***************************************************************************;

option nocenter missing=' ';

filename time_period "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/PRODUCTS/SIBLINGS/time_period.sas";
libname userstor "/opt/sas8.2/ud_templates" ;
ods path userstor.templat(read) sashelp.tmplmst(read) ;

proc format;
   picture ntemp (round) low-<0=' '     0-<5='000,009' 5-high='000,009';
   picture dtemp (round) low-<0=' '     0.0-high='009.9';
   value vsibs  .=' '           1='1'          2='2'
                3='3'           4='4'          5='5'
                6='6+'         -1='All Children'          0='2+ Children Total*';
   value veth  .='Total'           1='Black'            2='White'
               3='Latino'        4='Asian/PI'         5='Nat. Amer.'
			   6='Missing'         0='Total';
   value afcars 1='Pre-Adopt'     
                2='Relative/NREFM'               
                3='Foster'
                4='FFA'           
                9='Court Specified Home'   
               10='Group'
               11='Shelter'        
               13='Non-FC'            
         /*    14='Guardian'  */
               15='Guardian - Dependent'    
               16='Guardian - Non-Dependent'
               17='Runaway'      
               18='Trial Home Visit' 
               19='SILP'
               20='Transitional Housing'
               29='Other'
               99='Missing'       
                .='Total' ;
   value vcnty
      00='California'           01='Alameda'           02='Alpine'
      03='Amador'               04='Butte'             05='Calaveras'
      06='Colusa'               07='Contra Costa'      08='Del Norte'
      09='El Dorado'            10='Fresno'            11='Glenn'
      12='Humboldt'             13='Imperial'          14='Inyo'
      15='Kern'                 16='Kings'             17='Lake'
      18='Lassen'               19='Los Angeles'       20='Madera'
      21='Marin'                22='Mariposa'          23='Mendocino'
      24='Merced'               25='Modoc'             26='Mono'
      27='Monterey'             28='Napa'              29='Nevada'
      30='Orange'               31='Placer'            32='Plumas'
      33='Riverside'            34='Sacramento'        35='San Benito'
      36='San Bernardino'       37='San Diego'         38='San Francisco'
      39='San Joaquin'          40='San Luis Obispo'   41='San Mateo'
      42='Santa Barbara'        43='Santa Clara'       44='Santa Cruz'
      45='Shasta'               46='Sierra'            47='Siskiyou'
      48='Solano'               49='Sonoma'            50='Stanislaus'
      51='Sutter'               52='Tehama'            53='Trinity'
      54='Tulare'               55='Tuolumne'          56='Ventura'
      57='Yolo'                 58='Yuba'              59='Missing'
	  60='Missing'
      99='Missing';
run;


*******************************************************************************************;
************************************* Begin program ***************************************;
*******************************************************************************************;

*** Set the Assignment end date into the future for ease of sorting ***;
/*
data assign;
   set cws.ASGNM_T;

   if END_DT = . then END_DT = '31DEC3000'd;

run;

*** Join the CASE and ASSIGN tables together ***;

proc sql;

   create table CASE_ASSIGN as
   select x.FKCHLD_CLT, 
          x.IDENTIFIER as caseid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.CASE_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, END_DT, END_TM;

quit;

*** Join the REFERRAL and ASSIGN tables together ***;

proc sql;

   create table REFE_ASSIGN as
   select x.IDENTIFIER as refid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.REFERL_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'R'
   order by refid, START_DT, END_DT, END_TM;

quit;
*/


/*proc sort data=cws.plc_hm_t out=plchm; by identifier; run;
data plchm; set plchm; plchmvar=1; run;

proc sort data=elf.ucb_fc         out=seons; by fkclient_t pe_s_dt oh_s_dt; run;*/
proc sort data=temp.siblings_&qt out=sibs(rename=(fkclient=fkclient_t)); by fkclient; run;

*******************************************************************************************;
************************************* Begin Macro   ***************************************;
*******************************************************************************************;

%macro sibs(month, outmonth, year, outfile); 

*******************************************************************************************;
***** Include         PIT program here - to adjust the PIT numbers - added 12/04/2002 *****;
***** * Changed to       PIT method on 9/22/2003.  This new method calculates PIT     *****;
*****   based on the month and year listed at beginning of program.                   *****;
***** * Changed to updated PIT on 5/16/07 - include actual code in program also.      *****;
*******************************************************************************************;

*%pit(seons,&month.,&year.) ;  

/*change*/
data seons_pit (rename= (pit_plc=fed)); 
set dwh.ucb_pit; 
where agency = 1 and age le 17 and period_dt = "01&month.&year."d  /* '01JAN2019'D */ ; 

*if 1800 < county < 2000 then county = 1900;
run;


*******************************************************************************************;
***** Adjust the family count - it should be based on PIT file only and it is now     *****;
***** based on the entire ELF file.                                                   *****;
*******************************************************************************************;

proc sort data=seons_pit; by fkclient_t; run;

data fredrick wilma pebbles;
   merge sibs(in=a)
         seons_pit(in=b);
   by fkclient_t;
   if fam_count ge 6 then fam_count=6;
   if a and b then output fredrick;
   else if a and not b then output wilma;
   else if b and not a then output pebbles;
run;

data fredrick2;
   set fredrick;
   sup_cty=cnty_spfcd/1;
   county = input(cnty_spfcd,best2.);
   /*change*/*zipno=zip_no; 
   *drop zip_no;
run;

*** Remove ICPC children ***; /*unnecessary as ICPC children not in UCB_PIT*/

data fredrick3;
   length tfamid $12.;
   set fredrick2;
   if county=60 then delete; 
   /*if not (1 le county le 59) then county=60;*/
   tfamid=compress(put(county,z2.)||famid);
run;

proc sort data=fredrick3 nodupkey out=newfam(rename=(fkclient_t=fkclient)); by tfamid fkclient_t; run;

data newfam2(keep=tfamid count);
   set newfam;
   retain count;
   by tfamid;
   if first.tfamid then count=0;
   count=count+1;
   if last.tfamid then do;
      if count gt 6 then count=6;
      output;
   end;
run;

proc sort data=fredrick3; by tfamid;
proc sort data=newfam2; by tfamid;

data seons_pit2;
   merge fredrick3(rename=(fam_count=oldfamcount2)) newfam2;
   by tfamid;
run;

proc sort data=seons_pit2(rename=(count=fam_count));
by fkclient_t; * pe_s_dt oh_s_dt;/*change UCB_PIT does not include pe_s_dt or oh_s_dt as point in time only*/
run;

*******************************************************************************************;
***** Now that the correct sibling groups are included we can continue.               *****;
*******************************************************************************************;

/*change revised to use out of home placement table - only facility ID needed*/
/*proc sql;
   create table sibplchm as
   select a.*, b.*
   from seons_pit2 as a left join plchm as b
   on a.fkplc_hm_t=b.identifier ;
quit;*/
proc sql;
	create table sibplchm as
	select a.*, b.fkplc_hm_t
	from seons_pit2 as a left join cws.o_hm_plt as b
	on a.ohmpl_id = b.identifier;
	quit;

***** The file sibplchm contains all of the Siblings info and the PLCHM stuff *****;
***** The file sibplchm has 87,995 observations - one to one match.           *****;

/*changed*/proc sort data=sibplchm; by famid /*identifier oh_s_dt*/ fkplc_hm_t fkclient_t; run;

***** Sort the file by sibling id (famid), placement home (identifi), start   *****;
***** date (oh_s_dt) and child (fkclient).  I make several temporary variables*****;
***** to find if all of the children are placed together or not.              *****;

data multisib onlykids;
   length undupvar $50;
   set sibplchm;
   if fam_count lt 2 then output onlykids;
   else do;
      *undupvar=compress(compress(street_no)||compress(street_nm)||compress(zip_no)||compress(FACLTY_NM));
      /*change*/ *undupvar=identifier; undupvar=fkplc_hm_t; 
	 *** USe placement home ID instead of the address and name - no longer have access ;
      output multisib;
   end;
run;

***** The data step TRYIT creates counters based on the identifiers          *****;

proc sort data=multisib; by tfamid undupvar fkclient_t; run;

data tryit;
   length tempfk $10 counter 3.;
   set multisib;
   retain tempfk counter plccount /*tempdt*/;
   by tfamid undupvar fkclient_t;
   if first.tfamid then famcount=1;                          ***** Family counter for comparison            *****;
   if first.undupvar then do;
      counter=0; plccount=0; tempfk=' ' ; /*tempdt=oh_s_dt;*/    ***** clear retained variables                 *****;
      if not last.undupvar then do;                         ***** if only one child dont do anything        *****;
         tempfk=fkclient_t;
         counter=1; plccount=1;tmp1=1;
      end;
   end;
   else do;
      if fkclient_t ne tempfk then do;            ***** only increase counter for diff. kids     *****;
         if fkclient_t ne lag(fkclient_t) then
            counter=counter+1;
      end;
   end;
   if last.undupvar then do;                    ***** On last record make variables as approp. *****;
      if fam_count le counter then siball=1;    ***** If counter=fam_count then all            *****;
      else if counter gt 1 then do;             ***** If counter ge 2 (more than one kid,      *****;
         sibsome=1; somenum=counter;            *****    but not all of the kids) then some    *****;
      end;
   end;
run;

***** Interested in all children in a family to have the same all/some listing *****;

proc sort data=tryit; by tfamid undupvar descending counter;

data finsib;
   retain temppa tempps tempsn ;
   set tryit;
   by tfamid undupvar ;
   if first.undupvar then do; temppa=siball; tempps=sibsome; tempsn=somenum; end;
   else do; siball=temppa; sibsome=tempps; somenum=tempsn; end;
   drop temppa tempps tempsn ;
run;

proc sort data=finsib; by tfamid descending counter;

data finsib;
   retain sibfams;
   set finsib;
   by tfamid ;
   if first.tfamid then sibfams=.;
   if first.tfamid and siball=1 then sibfams=1;
   else if first.tfamid and sibsome=1 then sibfams=1;
run;

***** Instead of unduplicating - roll any duplicate placements up *****;

proc sort data=finsib; by fkclient_T; run;

data finsib2;
   length identifi2 $10;
   retain identifi2 /*pe_s_dt2 pe_e_dt2 oh_s_dt2 oh_e_dt2*/;
   set finsib;
   by fkclient_t;
   if first.fkclient_T and last.fkclient_t then do;
      /*pe_s_dt2=.; pe_e_dt2=.; oh_s_dt2=.; oh_e_dt2=.;*/
      identifi2=' '; output;
   end;
   else if first.fkclient_t and not last.fkclient_t then do; /*change*/identifi2=fkplc_hm_t;
      /*pe_s_dt2=pe_s_dt; pe_e_dt2=pe_e_dt; oh_s_dt2=oh_s_dt; oh_e_dt2=oh_e_dt;*/
   end;
   else if last.fkclient_t and not first.fkclient_t then output;
run;

***** Examine the number of sibling all/some categories compared to total *****;
***** but by the number of siblings in the system.                        *****;

proc summary data=finsib2;
   class fam_count;
   var siball sibsome sibfams somenum;
   output out=summres3 sum=;
run;

data summres3;
   set summres3;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** Put the sibfams variable on the only kids who have siblings with some placed together *****;

proc sort data=onlykids nodupkey; by fkclient_t; run;

***** Bring the files together *****;

data finale;
   set finsib2(in=a)
       onlykids(in=b);
   if siball ne 1 then siball=0;
   if sibsome ne 1 then sibsome=0;
   totplc=sum(siball,sibsome);
   if sup_cty lt 1 or sup_cty gt 58 then sup_cty=59;
run;

proc summary data=finale;
   class fam_count;
   var siball sibsome somenum;
   output out=summres4 sum=;
run;

data summres4;
   set summres4;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** List the number of Siblings by County *****;
***** First use valid county codes and other*****;

proc summary data=finale;
   class sup_cty fam_count;
   var siball sibsome somenum;
   output out=summres5 sum=;
run;

***** There will be two total lines - one for all kids and one for multi-siblings *****;

proc summary data=finale;
   class sup_cty fam_count;
   where fam_count gt 1;
   var siball sibsome somenum;
   output out=summres5c sum=;
run;

proc sort data=summres5c; by fam_count; where _type_ in (0,2); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data summres5b;
   length cntyname $27 newgvr $6;
   set summres5 summres5c(in=b);
   if b and sup_cty=. then sup_cty=0;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=summres5b; by newgvr fam_count; run;

***** The dataset siblingvars only has the matching variable and the flags. *****;

data pebbles;
   length tfamid $12.;
   set pebbles;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||fkclient_t);

run;

proc sort data=pebbles out=noelf; by fkclient_t; run;
proc sort data=noelf; by tfamid;
proc sort data=finale out=tf(keep=tfamid sibfams sup_cty); by tfamid; where sibfams=1; run;
proc sort data=tf nodupkey; by tfamid; run;

data noelf2;
   merge noelf(in=a) tf;
   by tfamid;
   if a;
run;

proc freq data=noelf2; table sup_cty /list missing; run;
data siblingvars;
   set finale noelf2;
   if siball=. then siball=0;
   if totplc=. then totplc=0;
   if sibfams=. then sibfams=0;
run;

*******************************************************************************************;
***** Totals by county                                                                *****;
*******************************************************************************************;

**************************************************************************************;
*****                                                                            *****;
***** Group the facility types into categories based on the following list:      *****;
*****       1='Pre-Adopt'                                                        *****;              
*****       2='Kin'                                                              *****;
*****       3='Foster'                                                           *****;
*****   3.1 4='FFA'                                                              *****;           
***** 3.5 5 9='Court Specified Home'                                             *****;
*****     9.2='Tribally Specified Home'                                          *****;
*****  4 6 10='Group'                                                            *****;
*****  5 7 11='Shelter'                                                          *****;          
***** 6 11 15='Guardian'                                                         *****; 
*****   12 16='Guardian-Other'                                                   *****;
*****      13='All Others' - including: 5.1 8 13=Non-FC, 6.1 99=Missing,         *****;
*****       7 13 17=Runaway, 8 14 18=Trial Home Visit, 9 15 19=SILP              *****;
*****      20=Transitional Housing 29=Other(?),                                  *****;
*****       10=ILSP,  12=Other-other                                             *****;
**************************************************************************************;

data sibkin;
   set siblingvars;
   if fed in (2,3,4,9,9.2,10,11,15,16) then tempfed=fed; 
   else tempfed=30;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where fam_count gt 1 and agency=1;
   var siball sibsome somenum;
   output out=fulld sum=;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where agency=1;
   var siball sibsome somenum;
   output out=fulle sum=;
run;

proc sort data=fulld; by fed fam_count; where _type_ in (0,2,4,6); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data fullf;
   length cntyname $27 newgvr $6;
   set fulle fulld(in=b);
   if b and sup_cty =. then sup_cty =0;
   if b and fam_count=. then fam_count=0;
   if not b and fam_count=. then fam_count=-1;
   pctall=siball/_freq_  *100 ;  pctsome=sibsome/_freq_ *100 ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_ * 100;
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=fullf; by sup_cty fed fam_count; run;

*******************************************************************************************;
*****  Create a filler table that has one record per county for the summary and       *****;
*****  one record per county per family size for the other reports.                   *****;
*******************************************************************************************;
data fullfb;
   set fullf;
   if sup_cty=. then sup_cty=0;
run;

proc sort data=fullfb; by sup_cty fed fam_count; run;

data template;
   do sup_cty=0 to 59;
      fed=.;    do fam_count=-1 to 6; output; end;
      fed=1;    do fam_count=-1 to 6; output; end;
      fed=2;    do fam_count=-1 to 6; output; end;
      fed=3;    do fam_count=-1 to 6; output; end;
      fed=4;    do fam_count=-1 to 6; output; end;
      fed=9;    do fam_count=-1 to 6; output; end;
      fed=9.2;  do fam_count=-1 to 6; output; end;
      fed=10;   do fam_count=-1 to 6; output; end;
      fed=11;   do fam_count=-1 to 6; output; end;
      fed=13;   do fam_count=-1 to 6; output; end;
 /*   fed=14;   do fam_count=-1 to 6; output; end;  */
      fed=15;   do fam_count=-1 to 6; output; end;
      fed=16;   do fam_count=-1 to 6; output; end;
      fed=17;   do fam_count=-1 to 6; output; end;
      fed=18;   do fam_count=-1 to 6; output; end;
      fed=19;   do fam_count=-1 to 6; output; end;
      fed=20;   do fam_count=-1 to 6; output; end;
      fed=29;   do fam_count=-1 to 6; output; end;
      fed=99;   do fam_count=-1 to 6; output; end;
   end;
run;

data fullg;
   length fedname $20;
   merge fullfb template;
   by sup_cty fed fam_count;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if first.fed then do;
      fedname=put(fed,afcars.);
      siball=.; pctall=.; sibsome=.; pctsome=.;  totplc=.; totpct=.;
   end;
   else do;
      fedname=' ';
	  if _freq_=. then _freq_=0;
	  if siball=. then siball=0;
	  if pctall=. then pctall=0.0;
	  if sibsome=. then sibsome=0;
	  if pctsome=. then pctsome=0.0;
	  if totplc=. then totplc=0;
	  if totpct=. then totpct=0.0;
   end;
run;

*******************************************************************************************;
*****  Summary tables                                                                 *****;
*******************************************************************************************;

%macro summary;

data sum_&outfile.(drop=fedname cntyname newgvr FED fam_count pqtr yr); 
   set fullg;
   where fam_count=0 and fed=.; 
   if cntyname=:'State' then cntyname='California';

pqtr = qtr("01&month.&year."d);
yr = year("01&month.&year."d);

PERIOD_DT = yyq(yr,pqtr);

rename
_FREQ_ = COUNT
sup_cty = COUNTY;

AGE_GRP = 1;
P_TYPE = 3;

run;

proc append base=local.siblings_al1 data = sum_&outfile.;

%mend summary;
%summary;
%mend sibs;

%include time_period;

/*
%sibs(jan, January, 2025, jan2025);

%sibs(oct, October, 2024, oct2024);
%sibs(jul, July,    2024, jul2024);
%sibs(apr, April,   2024, apr2024);
%sibs(jan, January, 2024, jan2024);

%sibs(oct, October, 2023, oct2023);
%sibs(jul, July,    2023, jul2023);
%sibs(apr, April,   2023, apr2023);
%sibs(jan, January, 2023, jan2023);

%sibs(oct, October, 2022, oct2022);
%sibs(jul, July,    2022, jul2022);
%sibs(apr, April,   2022, apr2022);
%sibs(jan, January, 2022, jan2022);

%sibs(oct, October, 2021, oct2021);
%sibs(jul, July,    2021, jul2021);
%sibs(apr, April,   2021, apr2021);
%sibs(jan, January, 2021, jan2021);

%sibs(oct, October, 2020, oct2020);
%sibs(jul, July,    2020, jul2020);
%sibs(apr, April,   2020, apr2020);
%sibs(jan, January, 2020, jan2020);

%sibs(oct, October, 2019, oct2019);
%sibs(jul, July,    2019, jul2019);
%sibs(apr, April,   2019, apr2019);
%sibs(jan, January, 2019, jan2019);

%sibs(oct, October, 2018, oct2018);
%sibs(jul, July,    2018, jul2018);
%sibs(apr, April,   2018, apr2018);
%sibs(jan, January, 2018, jan2018);

%sibs(oct, October, 2017, oct2017);
%sibs(jul, July,    2017, jul2017);
%sibs(apr, April,   2017, apr2017);
%sibs(jan, January, 2017, jan2017);

%sibs(oct, October, 2016, oct2016);
%sibs(jul, July,    2016, jul2016);
%sibs(apr, April,   2016, apr2016);
%sibs(jan, January, 2016, jan2016);

%sibs(oct, October, 2015, oct2015);
%sibs(jul, July,    2015, jul2015);
%sibs(apr, April,   2015, apr2015);
%sibs(jan, January, 2015, jan2015);

%sibs(oct, October, 2014, oct2014);
%sibs(jul, July,    2014, jul2014);
%sibs(apr, April,   2014, apr2014);
%sibs(jan, January, 2014, jan2014);

%sibs(oct, October, 2013, oct2013);
%sibs(jul, July,    2013, jul2013);
%sibs(apr, April,   2013, apr2013);
%sibs(jan, January, 2013, jan2013);

%sibs(oct, October, 2012, oct2012);
%sibs(jul, July,    2012, jul2012);
%sibs(apr, April,   2012, apr2012);
%sibs(jan, January, 2012, jan2012);

%sibs(oct, October, 2011, oct2011);
%sibs(jul, July,    2011, jul2011);
%sibs(apr, April,   2011, apr2011);
%sibs(jan, January, 2011, jan2011);

%sibs(oct, October, 2010, oct2010);
%sibs(jul, July,    2010, jul2010);
%sibs(apr, April,   2010, apr2010);
%sibs(jan, January, 2010, jan2010);

%sibs(oct, October, 2009, oct2009);
%sibs(jul, July,    2009, jul2009);
%sibs(apr, April,   2009, apr2009);
%sibs(jan, January, 2009, jan2009);

%sibs(oct, October, 2008, oct2008);
%sibs(jul, July,    2008, jul2008);
%sibs(apr, April,   2008, apr2008);
%sibs(jan, January, 2008, jan2008);
*/
***************************************************************************;
**                                                                       **;
** SIBLING_PIT_FILES.sas                                                 **;
**                                                                       **;
** Copyright (c) 2006 Center for Social Services Research,               **;
** University of California at Berkeley. All rights reserved.            **;
** Center for Social Services Research - Univ of California at Berkeley  **;
** CWS/CMS Reporting:  Sibling Analysis                                  **;
**                                                                       **;
** This program takes a PIT file - created using a macro embedded in the **;
** program and the Sibling ID file and combines them to specify whether  **;
** siblings are placed together or not.                                  **;
**                                                                       **;
** FOR:  Barbara Needell          BY: Terry Shaw                         **;
**                                                                       **;
** This program was updated on 12/04/2002 to include the new PIT code    **;
**    (changes in ILSP and Limbo kids) and to incorporate the Placement  **;
**    /Ethnicity/and Matrix reports into a single program.               **;
** This program was updated on 9/22/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 10/9/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 9/22/2004 to enclose the program in macro **;
**    code to facilitate running multiple time periods.                  **;
** This program was updated on 3/14/2005 to change the placement home    **;
**    matching process to use IDENTIFIER rather than the created place-  **;
**    home address indicator because of confidentiality.                 **;
** This program was updated on 1/30/2005 to change the macro calling the **;
**    point in time code to use the most recent version of that code.    **;
** This program was updated on 9/26/2006 to update the way that siblings **;
**    are categorized.  Siblings are now calculated at the county level  **;
**    rather than the state level because counties do not 'see' siblings **;
**    outside of their county boundaries.                                **;
**                                                                       **;
** The following files are created:                                      **;
**     SIBf_00_xxx200y_s.xml   (Summary file)                            **;
**     SIBf_P0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_E0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_00_xxx200y_0.xml                                             **;
**                                                                       **;
**  2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group   **;
***************************************************************************;

option nocenter missing=' ';

filename time_period "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/PRODUCTS/SIBLINGS/time_period.sas";
libname userstor "/opt/sas8.2/ud_templates" ;
ods path userstor.templat(read) sashelp.tmplmst(read) ;

proc format;
   picture ntemp (round) low-<0=' '     0-<5='000,009' 5-high='000,009';
   picture dtemp (round) low-<0=' '     0.0-high='009.9';
   value vsibs  .=' '           1='1'          2='2'
                3='3'           4='4'          5='5'
                6='6+'         -1='All Children'          0='2+ Children Total*';
   value veth  .='Total'           1='Black'            2='White'
               3='Latino'        4='Asian/PI'         5='Nat. Amer.'
			   6='Missing'         0='Total';
   value afcars 1='Pre-Adopt'     
                2='Relative/NREFM'               
                3='Foster'
                4='FFA'           
                9='Court Specified Home'   
              9.2='Tribally Specified Home'
               10='Group'
               11='Shelter'        
               13='Non-FC'            
         /*    14='Guardian'  */
               15='Guardian - Dependent'    
               16='Guardian - Non-Dependent'
               17='Runaway'      
               18='Trial Home Visit' 
               19='SILP'
               20='Transitional Housing'
               29='Other'
               99='Missing'       
                .='Total' ;
   value vcnty
      00='California'           01='Alameda'           02='Alpine'
      03='Amador'               04='Butte'             05='Calaveras'
      06='Colusa'               07='Contra Costa'      08='Del Norte'
      09='El Dorado'            10='Fresno'            11='Glenn'
      12='Humboldt'             13='Imperial'          14='Inyo'
      15='Kern'                 16='Kings'             17='Lake'
      18='Lassen'               19='Los Angeles'       20='Madera'
      21='Marin'                22='Mariposa'          23='Mendocino'
      24='Merced'               25='Modoc'             26='Mono'
      27='Monterey'             28='Napa'              29='Nevada'
      30='Orange'               31='Placer'            32='Plumas'
      33='Riverside'            34='Sacramento'        35='San Benito'
      36='San Bernardino'       37='San Diego'         38='San Francisco'
      39='San Joaquin'          40='San Luis Obispo'   41='San Mateo'
      42='Santa Barbara'        43='Santa Clara'       44='Santa Cruz'
      45='Shasta'               46='Sierra'            47='Siskiyou'
      48='Solano'               49='Sonoma'            50='Stanislaus'
      51='Sutter'               52='Tehama'            53='Trinity'
      54='Tulare'               55='Tuolumne'          56='Ventura'
      57='Yolo'                 58='Yuba'              59='Missing'
	  60='Missing'
      99='Missing';
run;


*******************************************************************************************;
************************************* Begin program ***************************************;
*******************************************************************************************;

*** Set the Assignment end date into the future for ease of sorting ***;
/*
data assign;
   set cws.ASGNM_T;

   if END_DT = . then END_DT = '31DEC3000'd;

run;

*** Join the CASE and ASSIGN tables together ***;

proc sql;

   create table CASE_ASSIGN as
   select x.FKCHLD_CLT, 
          x.IDENTIFIER as caseid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.CASE_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, END_DT, END_TM;

quit;

*** Join the REFERRAL and ASSIGN tables together ***;

proc sql;

   create table REFE_ASSIGN as
   select x.IDENTIFIER as refid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.REFERL_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'R'
   order by refid, START_DT, END_DT, END_TM;

quit;
*/

/*
proc sort data=cws.plc_hm_t out=plchm; by identifier; run;
data plchm; set plchm; plchmvar=1; run;

proc sort data=elf.ucb_fc         out=seons; by fkclient_t pe_s_dt oh_s_dt; run;
*/

proc sort data=temp.siblings_&qt out=sibs(rename=(fkclient=fkclient_t)); by fkclient; run;

*******************************************************************************************;
************************************* Begin Macro   ***************************************;
*******************************************************************************************;

%macro sibs(month, outmonth, year, outfile); 

*******************************************************************************************;
***** Include         PIT program here - to adjust the PIT numbers - added 12/04/2002 *****;
***** * Changed to       PIT method on 9/22/2003.  This new method calculates PIT     *****;
*****   based on the month and year listed at beginning of program.                   *****;
***** * Changed to updated PIT on 5/16/07 - include actual code in program also.      *****;
*******************************************************************************************;

*%pit(seons,&month.,&year.) ;  

/*change*/
data seons_pit (rename= (pit_plc=fed));
set dwh.ucb_pit;
where agency = 1 and age le 20 and period_dt = "01&month.&year."d  /* '01JAN2019'D */ ;

*if 1800 < county < 2000 then county = 1900;
run;


*******************************************************************************************;
***** Adjust the family count - it should be based on PIT file only and it is now     *****;
***** based on the entire ELF file.                                                   *****;
*******************************************************************************************;

proc sort data=seons_pit; by fkclient_t; run;

data fredrick wilma pebbles;
   merge sibs(in=a)
         seons_pit(in=b);
   by fkclient_t;
   if fam_count ge 6 then fam_count=6;
   if a and b then output fredrick;
   else if a and not b then output wilma;
   else if b and not a then output pebbles;
run;

data fredrick2;
   set fredrick;
   sup_cty=cnty_spfcd/1;
   county = input(cnty_spfcd,best2.);
   /*change*/*zipno=zip_no;
   *drop zip_no;
run;

*** Remove ICPC children ***;

data fredrick3;
   length tfamid $12.;
   set fredrick2;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||famid);
run;

proc sort data=fredrick3 nodupkey out=newfam(rename=(fkclient_t=fkclient)); by tfamid fkclient_t; run;

data newfam2(keep=tfamid count);
   set newfam;
   retain count;
   by tfamid;
   if first.tfamid then count=0;
   count=count+1;
   if last.tfamid then do;
      if count gt 6 then count=6;
      output;
   end;
run;

proc sort data=fredrick3; by tfamid;
proc sort data=newfam2; by tfamid;

data seons_pit2;
   merge fredrick3(rename=(fam_count=oldfamcount2)) newfam2;
   by tfamid;
run;

proc sort data=seons_pit2(rename=(count=fam_count));
by fkclient_t; * pe_s_dt oh_s_dt;/*change UCB_PIT does not include pe_s_dt or oh_s_dt as point in time only*/
run;

*******************************************************************************************;
***** Now that the correct sibling groups are included we can continue.               *****;
*******************************************************************************************;

/*change revised to use out of home placement table - only facility ID needed*/
/*proc sql;
   create table sibplchm as
   select a.*, b.*
   from seons_pit2 as a left join plchm as b
   on a.fkplc_hm_t=b.identifier ;
quit;*/
proc sql;
        create table sibplchm as
        select a.*, b.fkplc_hm_t
        from seons_pit2 as a left join cws.o_hm_plt as b
        on a.ohmpl_id = b.identifier;
        quit;

***** The file sibplchm contains all of the Siblings info and the PLCHM stuff *****;
***** The file sibplchm has 87,995 observations - one to one match.           *****;

/*changed*/proc sort data=sibplchm; by famid /*identifier oh_s_dt*/ fkplc_hm_t fkclient_t; run;

***** Sort the file by sibling id (famid), placement home (identifi), start   *****;
***** date (oh_s_dt) and child (fkclient).  I make several temporary variables*****;
***** to find if all of the children are placed together or not.              *****;

data multisib onlykids;
   length undupvar $50;
   set sibplchm;
   if fam_count lt 2 then output onlykids;
   else do;
      *undupvar=compress(compress(street_no)||compress(street_nm)||compress(zip_no)||compress(FACLTY_NM));
      /*change*/ *undupvar=identifier; undupvar=fkplc_hm_t;
      *** USe placement home ID instead of the address and name - no longer have access ;
      output multisib;
   end;
run;

***** The data step TRYIT creates counters based on the identifiers          *****;

proc sort data=multisib; by tfamid undupvar fkclient_t; run;

data tryit;
   length tempfk $10 counter 3.;
   set multisib;
   retain tempfk counter plccount tempdt;
   by tfamid undupvar fkclient_t;
   if first.tfamid then famcount=1;                          ***** Family counter for comparison            *****;
   if first.undupvar then do;
      counter=0; plccount=0; tempfk=' ' ; tempdt=oh_s_dt;    ***** clear retained variables                 *****;
      if not last.undupvar then do;                         ***** if only one child dont do anything        *****;
         tempfk=fkclient_t;
         counter=1; plccount=1;tmp1=1;
      end;
   end;
   else do;
      if fkclient_t ne tempfk then do;            ***** only increase counter for diff. kids     *****;
         if fkclient_t ne lag(fkclient_t) then
            counter=counter+1;
      end;
   end;
   if last.undupvar then do;                    ***** On last record make variables as approp. *****;
      if fam_count le counter then siball=1;    ***** If counter=fam_count then all            *****;
      else if counter gt 1 then do;             ***** If counter ge 2 (more than one kid,      *****;
         sibsome=1; somenum=counter;            *****    but not all of the kids) then some    *****;
      end;
   end;
run;

***** Interested in all children in a family to have the same all/some listing *****;

proc sort data=tryit; by tfamid undupvar descending counter;

data finsib;
   retain temppa tempps tempsn ;
   set tryit;
   by tfamid undupvar ;
   if first.undupvar then do; temppa=siball; tempps=sibsome; tempsn=somenum; end;
   else do; siball=temppa; sibsome=tempps; somenum=tempsn; end;
   drop temppa tempps tempsn ;
run;

proc sort data=finsib; by tfamid descending counter;

data finsib;
   retain sibfams;
   set finsib;
   by tfamid ;
   if first.tfamid then sibfams=.;
   if first.tfamid and siball=1 then sibfams=1;
   else if first.tfamid and sibsome=1 then sibfams=1;
run;

***** Instead of unduplicating - roll any duplicate placements up *****;

proc sort data=finsib; by fkclient_T; run;

data finsib2;
   length identifi2 $10;
   retain identifi2 pe_s_dt2 pe_e_dt2 oh_s_dt2 oh_e_dt2;
   set finsib;
   by fkclient_t;
   if first.fkclient_T and last.fkclient_t then do;
      pe_s_dt2=.; pe_e_dt2=.; oh_s_dt2=.; oh_e_dt2=.;
      identifi2=' '; output;
   end;
   else if first.fkclient_t and not last.fkclient_t then do; /*change*/identifi2=fkplc_hm_t;
      /*pe_s_dt2=pe_s_dt; pe_e_dt2=pe_e_dt; oh_s_dt2=oh_s_dt; oh_e_dt2=oh_e_dt;*/
   end;
   else if last.fkclient_t and not first.fkclient_t then output;
run;

***** Examine the number of sibling all/some categories compared to total *****;
***** but by the number of siblings in the system.                        *****;

proc summary data=finsib2;
   class fam_count;
   var siball sibsome sibfams somenum;
   output out=summres3 sum=;
run;

data summres3;
   set summres3;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** Put the sibfams variable on the only kids who have siblings with some placed together *****;

proc sort data=onlykids nodupkey; by fkclient_t; run;

***** Bring the files together *****;

data finale;
   set finsib2(in=a)
       onlykids(in=b);
   if siball ne 1 then siball=0;
   if sibsome ne 1 then sibsome=0;
   totplc=sum(siball,sibsome);
   if sup_cty lt 1 or sup_cty gt 58 then sup_cty=59;
run;

proc summary data=finale;
   class fam_count;
   var siball sibsome somenum;
   output out=summres4 sum=;
run;

data summres4;
   set summres4;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** List the number of Siblings by County *****;
***** First use valid county codes and other*****;

proc summary data=finale;
   class sup_cty fam_count;
   var siball sibsome somenum;
   output out=summres5 sum=;
run;

***** There will be two total lines - one for all kids and one for multi-siblings *****;

proc summary data=finale;
   class sup_cty fam_count;
   where fam_count gt 1;
   var siball sibsome somenum;
   output out=summres5c sum=;
run;

proc sort data=summres5c; by fam_count; where _type_ in (0,2); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data summres5b;
   length cntyname $27 newgvr $6;
   set summres5 summres5c(in=b);
   if b and sup_cty=. then sup_cty=0;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=summres5b; by newgvr fam_count; run;

***** The dataset siblingvars only has the matching variable and the flags. *****;

data pebbles;
   length tfamid $12.;
   set pebbles;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||fkclient_t);

run;

proc sort data=pebbles out=noelf; by fkclient_t; run;
proc sort data=noelf; by tfamid;
proc sort data=finale out=tf(keep=tfamid sibfams sup_cty); by tfamid; where sibfams=1; run;
proc sort data=tf nodupkey; by tfamid; run;

data noelf2;
   merge noelf(in=a) tf;
   by tfamid;
   if a;
run;

proc freq data=noelf2; table sup_cty /list missing; run;
data siblingvars;
   set finale noelf2;
   if siball=. then siball=0;
   if totplc=. then totplc=0;
   if sibfams=. then sibfams=0;
run;

*******************************************************************************************;
***** Totals by county                                                                *****;
*******************************************************************************************;

**************************************************************************************;
*****                                                                            *****;
***** Group the facility types into categories based on the following list:      *****;
*****       1='Pre-Adopt'                                                        *****;              
*****       2='Kin'                                                              *****;
*****       3='Foster'                                                           *****;
*****   3.1 4='FFA'                                                              *****;           
***** 3.5 5 9='Court Specified Home'                                             *****;
*****     9.2='Tribally Specified Home'                                          *****;
*****  4 6 10='Group'                                                            *****;
*****  5 7 11='Shelter'                                                          *****;          
***** 6 11 15='Guardian'                                                         *****; 
*****   12 16='Guardian-Other'                                                   *****;
*****      13='All Others' - including: 5.1 8 13=Non-FC, 6.1 99=Missing,         *****;
*****       7 13 17=Runaway, 8 14 18=Trial Home Visit, 9 15 19=SILP              *****;
*****      20=Transitional Housing 29=Other(?),                                  *****;
*****       10=ILSP,  12=Other-other                                             *****;
**************************************************************************************;

data sibkin;
   set siblingvars;
   if fed in (2,3,4,9,9.2,10,11,15,16) then tempfed=fed; 
   else tempfed=30;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where fam_count gt 1 and agency=1;
   var siball sibsome somenum;
   output out=fulld sum=;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where agency=1;
   var siball sibsome somenum;
   output out=fulle sum=;
run;

proc sort data=fulld; by fed fam_count; where _type_ in (0,2,4,6); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data fullf;
   length cntyname $27 newgvr $6;
   set fulle fulld(in=b);
   if b and sup_cty =. then sup_cty =0;
   if b and fam_count=. then fam_count=0;
   if not b and fam_count=. then fam_count=-1;
   pctall=siball/_freq_  *100 ;  pctsome=sibsome/_freq_ *100 ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_ * 100;
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=fullf; by sup_cty fed fam_count; run;

*******************************************************************************************;
*****  Create a filler table that has one record per county for the summary and       *****;
*****  one record per county per family size for the other reports.                   *****;
*******************************************************************************************;
data fullfb;
   set fullf;
   if sup_cty=. then sup_cty=0;
run;

proc sort data=fullfb; by sup_cty fed fam_count; run;

data template;
   do sup_cty=0 to 59;
      fed=.;    do fam_count=-1 to 6; output; end;
      fed=1;    do fam_count=-1 to 6; output; end;
      fed=2;    do fam_count=-1 to 6; output; end;
      fed=3;    do fam_count=-1 to 6; output; end;
      fed=4;    do fam_count=-1 to 6; output; end;
      fed=9;    do fam_count=-1 to 6; output; end;
      fed=9.2;  do fam_count=-1 to 6; output; end;
      fed=10;   do fam_count=-1 to 6; output; end;
      fed=11;   do fam_count=-1 to 6; output; end;
      fed=13;   do fam_count=-1 to 6; output; end;
 /*   fed=14;   do fam_count=-1 to 6; output; end;  */
      fed=15;   do fam_count=-1 to 6; output; end;
      fed=16;   do fam_count=-1 to 6; output; end;
      fed=17;   do fam_count=-1 to 6; output; end;
      fed=18;   do fam_count=-1 to 6; output; end;
      fed=19;   do fam_count=-1 to 6; output; end;
      fed=20;   do fam_count=-1 to 6; output; end;
      fed=29;   do fam_count=-1 to 6; output; end;
      fed=99;   do fam_count=-1 to 6; output; end;
   end;
run;

data fullg;
   length fedname $20;
   merge fullfb template;
   by sup_cty fed fam_count;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if first.fed then do;
      fedname=put(fed,afcars.);
      siball=.; pctall=.; sibsome=.; pctsome=.;  totplc=.; totpct=.;
   end;
   else do;
      fedname=' ';
	  if _freq_=. then _freq_=0;
	  if siball=. then siball=0;
	  if pctall=. then pctall=0.0;
	  if sibsome=. then sibsome=0;
	  if pctsome=. then pctsome=0.0;
	  if totplc=. then totplc=0;
	  if totpct=. then totpct=0.0;
   end;
run;

*******************************************************************************************;
*****  Summary tables                                                                 *****;
*******************************************************************************************;

%macro summary;

data sum_&outfile.(drop=fedname cntyname newgvr FED fam_count pqtr yr); 
   set fullg;
   where fam_count=0 and fed=.; 
   if cntyname=:'State' then cntyname='California';

pqtr = qtr("01&month.&year."d);
yr = year("01&month.&year."d);

PERIOD_DT = yyq(yr,pqtr);

rename
_FREQ_ = COUNT
sup_cty = COUNTY;

AGE_GRP = 2;
P_TYPE = 3;

run;

proc append base=local.siblings_al2 data = sum_&outfile.;

%mend summary;
%summary;
%mend sibs;

%include time_period;

/*
%sibs(jan, January, 2025, jan2025);

%sibs(oct, October, 2024, oct2024);
%sibs(jul, July,    2024, jul2024);
%sibs(jan, January, 2024, jan2024);
%sibs(apr, April,   2024, apr2024);

%sibs(oct, October, 2023, oct2023);
%sibs(jul, July,    2023, jul2023);
%sibs(apr, April,   2023, apr2023);
%sibs(jan, January, 2023, jan2023);

%sibs(oct, October, 2022, oct2022);
%sibs(jul, July,    2022, jul2022);
%sibs(apr, April,   2022, apr2022);
%sibs(jan, January, 2022, jan2022);

%sibs(oct, October, 2021, oct2021);
%sibs(jul, July,    2021, jul2021);
%sibs(apr, April,   2021, apr2021);
%sibs(jan, January, 2021, jan2021);

%sibs(oct, October, 2020, oct2020);
%sibs(jul, July,    2020, jul2020);
%sibs(apr, April,   2020, apr2020);
%sibs(jan, January, 2020, jan2020);

%sibs(oct, October, 2019, oct2019);
%sibs(jul, July,    2019, jul2019);
%sibs(apr, April,   2019, apr2019);
%sibs(jan, January, 2019, jan2019);

%sibs(oct, October, 2018, oct2018);
%sibs(jul, July,    2018, jul2018);
%sibs(apr, April,   2018, apr2018);
%sibs(jan, January, 2018, jan2018);

%sibs(oct, October, 2017, oct2017);
%sibs(jul, July,    2017, jul2017);
%sibs(apr, April,   2017, apr2017);
%sibs(jan, January, 2017, jan2017);

%sibs(oct, October, 2016, oct2016);
%sibs(jul, July,    2016, jul2016);
%sibs(apr, April,   2016, apr2016);
%sibs(jan, January, 2016, jan2016);

%sibs(oct, October, 2015, oct2015);
%sibs(jul, July,    2015, jul2015);
%sibs(apr, April,   2015, apr2015);
%sibs(jan, January, 2015, jan2015);

%sibs(oct, October, 2014, oct2014);
%sibs(jul, July,    2014, jul2014);
%sibs(apr, April,   2014, apr2014);
%sibs(jan, January, 2014, jan2014);

%sibs(oct, October, 2013, oct2013);
%sibs(jul, July,    2013, jul2013);
%sibs(apr, April,   2013, apr2013);
%sibs(jan, January, 2013, jan2013);

%sibs(oct, October, 2012, oct2012);
%sibs(jul, July,    2012, jul2012);
%sibs(apr, April,   2012, apr2012);
%sibs(jan, January, 2012, jan2012);

%sibs(oct, October, 2011, oct2011);
%sibs(jul, July,    2011, jul2011);
%sibs(apr, April,   2011, apr2011);
%sibs(jan, January, 2011, jan2011);

%sibs(oct, October, 2010, oct2010);
%sibs(jul, July,    2010, jul2010);
%sibs(apr, April,   2010, apr2010);
%sibs(jan, January, 2010, jan2010);

%sibs(oct, October, 2009, oct2009);
%sibs(jul, July,    2009, jul2009);
%sibs(apr, April,   2009, apr2009);
%sibs(jan, January, 2009, jan2009);

%sibs(oct, October, 2008, oct2008);
%sibs(jul, July,    2008, jul2008);
%sibs(apr, April,   2008, apr2008);
%sibs(jan, January, 2008, jan2008);
*/
***************************************************************************;
**                                                                       **;
** SIBLING_PIT_FILES.sas                                                 **;
**                                                                       **;
** Copyright (c) 2006 Center for Social Services Research,               **;
** University of California at Berkeley. All rights reserved.            **;
** Center for Social Services Research - Univ of California at Berkeley  **;
** CWS/CMS Reporting:  Sibling Analysis                                  **;
**                                                                       **;
** This program takes a PIT file - created using a macro embedded in the **;
** program and the Sibling ID file and combines them to specify whether  **;
** siblings are placed together or not.                                  **;
**                                                                       **;
** FOR:  Barbara Needell          BY: Terry Shaw                         **;
**                                                                       **;
** This program was updated on 12/04/2002 to include the new PIT code    **;
**    (changes in ILSP and Limbo kids) and to incorporate the Placement  **;
**    /Ethnicity/and Matrix reports into a single program.               **;
** This program was updated on 9/22/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 10/9/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 9/22/2004 to enclose the program in macro **;
**    code to facilitate running multiple time periods.                  **;
** This program was updated on 3/14/2005 to change the placement home    **;
**    matching process to use IDENTIFIER rather than the created place-  **;
**    home address indicator because of confidentiality.                 **;
** This program was updated on 1/30/2005 to change the macro calling the **;
**    point in time code to use the most recent version of that code.    **;
** This program was updated on 9/26/2006 to update the way that siblings **;
**    are categorized.  Siblings are now calculated at the county level  **;
**    rather than the state level because counties do not 'see' siblings **;
**    outside of their county boundaries.                                **;
**                                                                       **;
** The following files are created:                                      **;
**     SIBf_00_xxx200y_s.xml   (Summary file)                            **;
**     SIBf_P0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_E0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_00_xxx200y_0.xml                                             **;
**                                                                       **;
**  2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group   **;
***************************************************************************;

option nocenter missing=' ';

filename time_period "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/PRODUCTS/SIBLINGS/time_period.sas";
libname userstor "/opt/sas8.2/ud_templates" ;
ods path userstor.templat(read) sashelp.tmplmst(read) ;

proc format;
   picture ntemp (round) low-<0=' '     0-<5='000,009' 5-high='000,009';
   picture dtemp (round) low-<0=' '     0.0-high='009.9';
   value vsibs  .=' '           1='1'          2='2'
                3='3'           4='4'          5='5'
                6='6+'         -1='All Children'          0='2+ Children Total*';
   value veth  .='Total'           1='Black'            2='White'
               3='Latino'        4='Asian/PI'         5='Nat. Amer.'
			   6='Missing'         0='Total';
   value afcars 1='Pre-Adopt'     
                2='Relative/NREFM'               
                3='Foster'
                4='FFA'           
                9='Court Specified Home'   
               10='Group'
               11='Shelter'        
               13='Non-FC'            
         /*    14='Guardian'  */
               15='Guardian - Dependent'    
               16='Guardian - Non-Dependent'
               17='Runaway'      
               18='Trial Home Visit' 
               19='SILP'
               20='Transitional Housing'
               29='Other'
               99='Missing'       
                .='Total' ;
   value vcnty
      00='California'           01='Alameda'           02='Alpine'
      03='Amador'               04='Butte'             05='Calaveras'
      06='Colusa'               07='Contra Costa'      08='Del Norte'
      09='El Dorado'            10='Fresno'            11='Glenn'
      12='Humboldt'             13='Imperial'          14='Inyo'
      15='Kern'                 16='Kings'             17='Lake'
      18='Lassen'               19='Los Angeles'       20='Madera'
      21='Marin'                22='Mariposa'          23='Mendocino'
      24='Merced'               25='Modoc'             26='Mono'
      27='Monterey'             28='Napa'              29='Nevada'
      30='Orange'               31='Placer'            32='Plumas'
      33='Riverside'            34='Sacramento'        35='San Benito'
      36='San Bernardino'       37='San Diego'         38='San Francisco'
      39='San Joaquin'          40='San Luis Obispo'   41='San Mateo'
      42='Santa Barbara'        43='Santa Clara'       44='Santa Cruz'
      45='Shasta'               46='Sierra'            47='Siskiyou'
      48='Solano'               49='Sonoma'            50='Stanislaus'
      51='Sutter'               52='Tehama'            53='Trinity'
      54='Tulare'               55='Tuolumne'          56='Ventura'
      57='Yolo'                 58='Yuba'              59='Missing'
	  60='Missing'
      99='Missing';
run;


*******************************************************************************************;
************************************* Begin program ***************************************;
*******************************************************************************************;

*** Set the Assignment end date into the future for ease of sorting ***;
/*
data assign;
   set cws.ASGNM_T;

   if END_DT = . then END_DT = '31DEC3000'd;

run;

*** Join the CASE and ASSIGN tables together ***;

proc sql;

   create table CASE_ASSIGN as
   select x.FKCHLD_CLT, 
          x.IDENTIFIER as caseid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.CASE_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, END_DT, END_TM;

quit;

*** Join the REFERRAL and ASSIGN tables together ***;

proc sql;

   create table REFE_ASSIGN as
   select x.IDENTIFIER as refid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.REFERL_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'R'
   order by refid, START_DT, END_DT, END_TM;

quit;
*/


/*proc sort data=cws.plc_hm_t out=plchm; by identifier; run;
data plchm; set plchm; plchmvar=1; run;

proc sort data=elf.ucb_fc         out=seons; by fkclient_t pe_s_dt oh_s_dt; run;*/
proc sort data=temp.siblings_&qt out=sibs(rename=(fkclient=fkclient_t)); by fkclient; run;

*******************************************************************************************;
************************************* Begin Macro   ***************************************;
*******************************************************************************************;

%macro sibs(month, outmonth, year, outfile); 

*******************************************************************************************;
***** Include         PIT program here - to adjust the PIT numbers - added 12/04/2002 *****;
***** * Changed to       PIT method on 9/22/2003.  This new method calculates PIT     *****;
*****   based on the month and year listed at beginning of program.                   *****;
***** * Changed to updated PIT on 5/16/07 - include actual code in program also.      *****;
*******************************************************************************************;

/*change*/
data seons_pit (rename= (pit_plc=fed));
set dwh.ucb_pit;
where agency = 1 and age le 17 and period_dt = "01&month.&year."d  /* '01JAN2019'D */ ;

*if 1800 < county < 2000 then county = 1900;
run;

*******************************************************************************************;
***** Adjust the family count - it should be based on PIT file only and it is now     *****;
***** based on the entire ELF file.                                                   *****;
*******************************************************************************************;

proc sort data=seons_pit; by fkclient_t; run;

data fredrick wilma pebbles;
   merge sibs(in=a)
         seons_pit(in=b);
   by fkclient_t;
   if fam_count ge 6 then fam_count=6;
   if a and b then output fredrick;
   else if a and not b then output wilma;
   else if b and not a then output pebbles;
run;

data fredrick2;
   set fredrick;
   sup_cty=cnty_spfcd/1;
   county = input(cnty_spfcd,best2.);
   /*change*/*zipno=zip_no;
   *drop zip_no;
run;

*** Remove ICPC children ***;

data fredrick3;
   length tfamid $12.;
   set fredrick2;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||famid);
run;

proc sort data=fredrick3 nodupkey out=newfam(rename=(fkclient_t=fkclient)); by tfamid fkclient_t; run;

data newfam2(keep=tfamid count);
   set newfam;
   retain count;
   by tfamid;
   if first.tfamid then count=0;
   count=count+1;
   if last.tfamid then do;
      if count gt 6 then count=6;
      output;
   end;
run;

proc sort data=fredrick3; by tfamid;
proc sort data=newfam2; by tfamid;

data seons_pit2;
   merge fredrick3(rename=(fam_count=oldfamcount2)) newfam2;
   by tfamid;
run;

proc sort data=seons_pit2(rename=(count=fam_count));
by fkclient_t /* pe_s_dt oh_s_dt */;
run;

*******************************************************************************************;
***** Now that the correct sibling groups are included we can continue.               *****;
*******************************************************************************************;

/*change revised to use out of home placement table - only facility ID needed*/
/*proc sql;
   create table sibplchm as
   select a.*, b.*
   from seons_pit2 as a left join plchm as b
   on a.fkplc_hm_t=b.identifier ;
quit;*/
proc sql;
        create table sibplchm as
        select a.*, b.fkplc_hm_t
        from seons_pit2 as a left join cws.o_hm_plt as b
        on a.ohmpl_id = b.identifier;
        quit;

***** The file sibplchm contains all of the Siblings info and the PLCHM stuff *****;
***** The file sibplchm has 87,995 observations - one to one match.           *****;

/*changed*/proc sort data=sibplchm; by famid /*identifier oh_s_dt*/ fkplc_hm_t fkclient_t; run;

***** Sort the file by sibling id (famid), placement home (identifi), start   *****;
***** date (oh_s_dt) and child (fkclient).  I make several temporary variables*****;
***** to find if all of the children are placed together or not.              *****;

data multisib onlykids;
   length undupvar $50;
   set sibplchm;
   if fam_count lt 2 then output onlykids;
   else do;
      *undupvar=compress(compress(street_no)||compress(street_nm)||compress(zip_no)||compress(FACLTY_NM));
      /*change*/ *undupvar=identifier; undupvar=fkplc_hm_t;
         *** USe placement home ID instead of the address and name - no longer have access ;
      output multisib;
   end;
run;

***** The data step TRYIT creates counters based on the identifiers          *****;

proc sort data=multisib; by tfamid undupvar fkclient_t; run;

data tryit;
   length tempfk $10 counter 3.;
   set multisib;
   retain tempfk counter plccount /* tempdt */;
   by tfamid undupvar fkclient_t;
   if first.tfamid then famcount=1;                              ***** Family counter for comparison            *****;
   if first.undupvar then do;
      counter=0; plccount=0; tempfk=' ' ; /* tempdt=oh_s_dt; */  ***** clear retained variables                 *****;
      if not last.undupvar then do;                              ***** if only one child dont do anything        *****;
         tempfk=fkclient_t;
         counter=1; plccount=1;tmp1=1;
      end;
   end;
   else do;
      if fkclient_t ne tempfk then do;            ***** only increase counter for diff. kids     *****;
         if fkclient_t ne lag(fkclient_t) then
            counter=counter+1;
      end;
   end;
   if last.undupvar then do;                    ***** On last record make variables as approp. *****;
      if fam_count le counter then siball=1;    ***** If counter=fam_count then all            *****;
      else if counter gt 1 then do;             ***** If counter ge 2 (more than one kid,      *****;
         sibsome=1; somenum=counter;            *****    but not all of the kids) then some    *****;
      end;
   end;
run;

***** Interested in all children in a family to have the same all/some listing *****;

proc sort data=tryit; by tfamid undupvar descending counter;

data finsib;
   retain temppa tempps tempsn ;
   set tryit;
   by tfamid undupvar ;
   if first.undupvar then do; temppa=siball; tempps=sibsome; tempsn=somenum; end;
   else do; siball=temppa; sibsome=tempps; somenum=tempsn; end;
   drop temppa tempps tempsn ;
run;

proc sort data=finsib; by tfamid descending counter;

data finsib;
   retain sibfams;
   set finsib;
   by tfamid ;
   if first.tfamid then sibfams=.;
   if first.tfamid and siball=1 then sibfams=1;
   else if first.tfamid and sibsome=1 then sibfams=1;
run;

***** Instead of unduplicating - roll any duplicate placements up *****;

proc sort data=finsib; by fkclient_T; run;

data finsib2;
   length identifi2 $10;
   retain identifi2 /* pe_s_dt2 pe_e_dt2 oh_s_dt2 oh_e_dt2 */;
   set finsib;
   by fkclient_t;
   if first.fkclient_T and last.fkclient_t then do;
   /* pe_s_dt2=.; pe_e_dt2=.; oh_s_dt2=.; oh_e_dt2=.; */
      identifi2=' '; output;
   end;
   else if first.fkclient_t and not last.fkclient_t then do; /*change*/identifi2=fkplc_hm_t;
      /*pe_s_dt2=pe_s_dt; pe_e_dt2=pe_e_dt; oh_s_dt2=oh_s_dt; oh_e_dt2=oh_e_dt;*/
   end;
   else if last.fkclient_t and not first.fkclient_t then output;
run;

***** Examine the number of sibling all/some categories compared to total *****;
***** but by the number of siblings in the system.                        *****;

proc summary data=finsib2;
   class fam_count;
   var siball sibsome sibfams somenum;
   output out=summres3 sum=;
run;

data summres3;
   set summres3;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** Put the sibfams variable on the only kids who have siblings with some placed together *****;

proc sort data=onlykids nodupkey; by fkclient_t; run;

***** Bring the files together *****;

data finale;
   set finsib2(in=a)
       onlykids(in=b);

/* Cut Nonrelative Placement */

   where FED not in (2, 9.2, 99);

   if siball ne 1 then siball=0;
   if sibsome ne 1 then sibsome=0;
   totplc=sum(siball,sibsome);
   if sup_cty lt 1 or sup_cty gt 58 then sup_cty=59;
run;

proc summary data=finale;
   class fam_count;
   var siball sibsome somenum;
   output out=summres4 sum=;
run;

data summres4;
   set summres4;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** List the number of Siblings by County *****;
***** First use valid county codes and other*****;

proc summary data=finale;
   class sup_cty fam_count;
   var siball sibsome somenum;
   output out=summres5 sum=;
run;

***** There will be two total lines - one for all kids and one for multi-siblings *****;

proc summary data=finale;
   class sup_cty fam_count;
   where fam_count gt 1;
   var siball sibsome somenum;
   output out=summres5c sum=;
run;

proc sort data=summres5c; by fam_count; where _type_ in (0,2); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data summres5b;
   length cntyname $27 newgvr $6;
   set summres5 summres5c(in=b);
   if b and sup_cty=. then sup_cty=0;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=summres5b; by newgvr fam_count; run;

***** The dataset siblingvars only has the matching variable and the flags. *****;

data pebbles;
   length tfamid $12.;
   set pebbles;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||fkclient_t);

run;

proc sort data=pebbles out=noelf; by fkclient_t; run;
proc sort data=noelf; by tfamid;
proc sort data=finale out=tf(keep=tfamid sibfams sup_cty); by tfamid; where sibfams=1; run;
proc sort data=tf nodupkey; by tfamid; run;

data noelf2;
   merge noelf(in=a) tf;
   by tfamid;
   if a;
run;

proc freq data=noelf2; table sup_cty /list missing; run;
data siblingvars;
   set finale noelf2;
   if siball=. then siball=0;
   if totplc=. then totplc=0;
   if sibfams=. then sibfams=0;
run;

*******************************************************************************************;
***** Totals by county                                                                *****;
*******************************************************************************************;

**************************************************************************************;
*****                                                                            *****;
***** Group the facility types into categories based on the following list:      *****;
*****       1='Pre-Adopt'                                                        *****;              
*****       2='Kin'                                                              *****;
*****       3='Foster'                                                           *****;
*****   3.1 4='FFA'                                                              *****;           
***** 3.5 5 9='Court Specified Home'                                             *****;
*****     9.2='Tribally Specified Home'                                          *****;
*****  4 6 10='Group'                                                            *****;
*****  5 7 11='Shelter'                                                          *****;          
***** 6 11 15='Guardian'                                                         *****; 
*****   12 16='Guardian-Other'                                                   *****;
*****      13='All Others' - including: 5.1 8 13=Non-FC, 6.1 99=Missing,         *****;
*****       7 13 17=Runaway, 8 14 18=Trial Home Visit, 9 15 19=SILP              *****;
*****      20=Transitional Housing 29=Other(?),                                  *****;
*****       10=ILSP,  12=Other-other                                             *****;
**************************************************************************************;

data sibkin;
   set siblingvars;
   if fed in (2,3,4,9,9.2,10,11,15,16) then tempfed=fed; 
   else tempfed=30;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where fam_count gt 1 and agency=1;
   var siball sibsome somenum;
   output out=fulld sum=;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where agency=1;
   var siball sibsome somenum;
   output out=fulle sum=;
run;

proc sort data=fulld; by fed fam_count; where _type_ in (0,2,4,6); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data fullf;
   length cntyname $27 newgvr $6;
   set fulle fulld(in=b);
   if b and sup_cty =. then sup_cty =0;
   if b and fam_count=. then fam_count=0;
   if not b and fam_count=. then fam_count=-1;
   pctall=siball/_freq_  *100 ;  pctsome=sibsome/_freq_ *100 ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_ * 100;
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=fullf; by sup_cty fed fam_count; run;

*******************************************************************************************;
*****  Create a filler table that has one record per county for the summary and       *****;
*****  one record per county per family size for the other reports.                   *****;
*******************************************************************************************;
data fullfb;
   set fullf;
   if sup_cty=. then sup_cty=0;
run;

proc sort data=fullfb; by sup_cty fed fam_count; run;

data template;
   do sup_cty=0 to 59;
      fed=.;    do fam_count=-1 to 6; output; end;
      fed=1;    do fam_count=-1 to 6; output; end;
      fed=2;    do fam_count=-1 to 6; output; end;
      fed=3;    do fam_count=-1 to 6; output; end;
      fed=4;    do fam_count=-1 to 6; output; end;
      fed=9;    do fam_count=-1 to 6; output; end;
      fed=9.2;  do fam_count=-1 to 6; output; end;
      fed=10;   do fam_count=-1 to 6; output; end;
      fed=11;   do fam_count=-1 to 6; output; end;
      fed=13;   do fam_count=-1 to 6; output; end;
 /*   fed=14;   do fam_count=-1 to 6; output; end;  */
      fed=15;   do fam_count=-1 to 6; output; end;
      fed=16;   do fam_count=-1 to 6; output; end;
      fed=17;   do fam_count=-1 to 6; output; end;
      fed=18;   do fam_count=-1 to 6; output; end;
      fed=19;   do fam_count=-1 to 6; output; end;
      fed=20;   do fam_count=-1 to 6; output; end;
      fed=29;   do fam_count=-1 to 6; output; end;
      fed=99;   do fam_count=-1 to 6; output; end;
   end;
run;

data fullg;
   length fedname $20;
   merge fullfb template;
   by sup_cty fed fam_count;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if first.fed then do;
      fedname=put(fed,afcars.);
      siball=.; pctall=.; sibsome=.; pctsome=.;  totplc=.; totpct=.;
   end;
   else do;
      fedname=' ';
	  if _freq_=. then _freq_=0;
	  if siball=. then siball=0;
	  if pctall=. then pctall=0.0;
	  if sibsome=. then sibsome=0;
	  if pctsome=. then pctsome=0.0;
	  if totplc=. then totplc=0;
	  if totpct=. then totpct=0.0;
   end;
run;

*******************************************************************************************;
*****  Summary tables                                                                 *****;
*******************************************************************************************;

%macro summary;

data sum_&outfile.(drop=fedname cntyname newgvr FED fam_count pqtr yr); 
   set fullg;
   where fam_count=0 and fed=.; 
   if cntyname=:'State' then cntyname='California';

pqtr = qtr("01&month.&year."d);
yr = year("01&month.&year."d);

PERIOD_DT = yyq(yr,pqtr);

rename
_FREQ_ = COUNT
sup_cty = COUNTY;

AGE_GRP = 1;
P_TYPE = 2;

run;

proc append base=local.siblings_nr1 data = sum_&outfile.;

%mend summary;
%summary;
%mend sibs;

%include time_period;

/*
%sibs(jan, January, 2025, jan2025);

%sibs(oct, October, 2024, oct2024);
%sibs(jul, July,    2024, jul2024);
%sibs(apr, April,   2024, apr2024);
%sibs(jan, January, 2024, jan2024);

%sibs(oct, October, 2023, oct2023);
%sibs(jul, July,    2023, jul2023);
%sibs(apr, April,   2023, apr2023);
%sibs(jan, January, 2023, jan2023);

%sibs(oct, October, 2022, oct2022);
%sibs(jul, July,    2022, jul2022);
%sibs(apr, April,   2022, apr2022);
%sibs(jan, January, 2022, jan2022);

%sibs(oct, October, 2021, oct2021);
%sibs(jul, July,    2021, jul2021);
%sibs(apr, April,   2021, apr2021);
%sibs(jan, January, 2021, jan2021);

%sibs(oct, October, 2020, oct2020);
%sibs(jul, July,    2020, jul2020);
%sibs(apr, April,   2020, apr2020);
%sibs(jan, January, 2020, jan2020);

%sibs(oct, October, 2019, oct2019);
%sibs(jul, July,    2019, jul2019);
%sibs(apr, April,   2019, apr2019);
%sibs(jan, January, 2019, jan2019);

%sibs(oct, October, 2018, oct2018);
%sibs(jul, July,    2018, jul2018);
%sibs(apr, April,   2018, apr2018);
%sibs(jan, January, 2018, jan2018);

%sibs(oct, October, 2017, oct2017);
%sibs(jul, July,    2017, jul2017);
%sibs(apr, April,   2017, apr2017);
%sibs(jan, January, 2017, jan2017);

%sibs(oct, October, 2016, oct2016);
%sibs(jul, July,    2016, jul2016);
%sibs(apr, April,   2016, apr2016);
%sibs(jan, January, 2016, jan2016);

%sibs(oct, October, 2015, oct2015);
%sibs(jul, July,    2015, jul2015);
%sibs(apr, April,   2015, apr2015);
%sibs(jan, January, 2015, jan2015);

%sibs(oct, October, 2014, oct2014);
%sibs(jul, July,    2014, jul2014);
%sibs(apr, April,   2014, apr2014);
%sibs(jan, January, 2014, jan2014);

%sibs(oct, October, 2013, oct2013);
%sibs(jul, July,    2013, jul2013);
%sibs(apr, April,   2013, apr2013);
%sibs(jan, January, 2013, jan2013);

%sibs(oct, October, 2012, oct2012);
%sibs(jul, July,    2012, jul2012);
%sibs(apr, April,   2012, apr2012);
%sibs(jan, January, 2012, jan2012);

%sibs(oct, October, 2011, oct2011);
%sibs(jul, July,    2011, jul2011);
%sibs(apr, April,   2011, apr2011);
%sibs(jan, January, 2011, jan2011);

%sibs(oct, October, 2010, oct2010);
%sibs(jul, July,    2010, jul2010);
%sibs(apr, April,   2010, apr2010);
%sibs(jan, January, 2010, jan2010);

%sibs(oct, October, 2009, oct2009);
%sibs(jul, July,    2009, jul2009);
%sibs(apr, April,   2009, apr2009);
%sibs(jan, January, 2009, jan2009);

%sibs(oct, October, 2008, oct2008);
%sibs(jul, July,    2008, jul2008);
%sibs(apr, April,   2008, apr2008);
%sibs(jan, January, 2008, jan2008);
*/
***************************************************************************;
**                                                                       **;
** SIBLING_PIT_FILES.sas                                                 **;
**                                                                       **;
** Copyright (c) 2006 Center for Social Services Research,               **;
** University of California at Berkeley. All rights reserved.            **;
** Center for Social Services Research - Univ of California at Berkeley  **;
** CWS/CMS Reporting:  Sibling Analysis                                  **;
**                                                                       **;
** This program takes a PIT file - created using a macro embedded in the **;
** program and the Sibling ID file and combines them to specify whether  **;
** siblings are placed together or not.                                  **;
**                                                                       **;
** FOR:  Barbara Needell          BY: Terry Shaw                         **;
**                                                                       **;
** This program was updated on 12/04/2002 to include the new PIT code    **;
**    (changes in ILSP and Limbo kids) and to incorporate the Placement  **;
**    /Ethnicity/and Matrix reports into a single program.               **;
** This program was updated on 9/22/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 10/9/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 9/22/2004 to enclose the program in macro **;
**    code to facilitate running multiple time periods.                  **;
** This program was updated on 3/14/2005 to change the placement home    **;
**    matching process to use IDENTIFIER rather than the created place-  **;
**    home address indicator because of confidentiality.                 **;
** This program was updated on 1/30/2005 to change the macro calling the **;
**    point in time code to use the most recent version of that code.    **;
** This program was updated on 9/26/2006 to update the way that siblings **;
**    are categorized.  Siblings are now calculated at the county level  **;
**    rather than the state level because counties do not 'see' siblings **;
**    outside of their county boundaries.                                **;
**                                                                       **;
** The following files are created:                                      **;
**     SIBf_00_xxx200y_s.xml   (Summary file)                            **;
**     SIBf_P0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_E0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_00_xxx200y_0.xml                                             **;
**                                                                       **;
**  2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group   **;
***************************************************************************;

option nocenter missing=' ';

filename time_period "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/PRODUCTS/SIBLINGS/time_period.sas";
libname userstor "/opt/sas8.2/ud_templates" ;
ods path userstor.templat(read) sashelp.tmplmst(read) ;

proc format;
   picture ntemp (round) low-<0=' '     0-<5='000,009' 5-high='000,009';
   picture dtemp (round) low-<0=' '     0.0-high='009.9';
   value vsibs  .=' '           1='1'          2='2'
                3='3'           4='4'          5='5'
                6='6+'         -1='All Children'          0='2+ Children Total*';
   value veth  .='Total'           1='Black'            2='White'
               3='Latino'        4='Asian/PI'         5='Nat. Amer.'
			   6='Missing'         0='Total';
   value afcars 1='Pre-Adopt'     
                2='Relative/NREFM'               
                3='Foster'
                4='FFA'           
                9='Court Specified Home'   
              9.2='Tribally Specified Home'
               10='Group'
               11='Shelter'        
               13='Non-FC'            
         /*    14='Guardian'  */
               15='Guardian - Dependent'    
               16='Guardian - Non-Dependent'
               17='Runaway'      
               18='Trial Home Visit' 
               19='SILP'
               20='Transitional Housing'
               29='Other'
               99='Missing'       
                .='Total' ;
   value vcnty
      00='California'           01='Alameda'           02='Alpine'
      03='Amador'               04='Butte'             05='Calaveras'
      06='Colusa'               07='Contra Costa'      08='Del Norte'
      09='El Dorado'            10='Fresno'            11='Glenn'
      12='Humboldt'             13='Imperial'          14='Inyo'
      15='Kern'                 16='Kings'             17='Lake'
      18='Lassen'               19='Los Angeles'       20='Madera'
      21='Marin'                22='Mariposa'          23='Mendocino'
      24='Merced'               25='Modoc'             26='Mono'
      27='Monterey'             28='Napa'              29='Nevada'
      30='Orange'               31='Placer'            32='Plumas'
      33='Riverside'            34='Sacramento'        35='San Benito'
      36='San Bernardino'       37='San Diego'         38='San Francisco'
      39='San Joaquin'          40='San Luis Obispo'   41='San Mateo'
      42='Santa Barbara'        43='Santa Clara'       44='Santa Cruz'
      45='Shasta'               46='Sierra'            47='Siskiyou'
      48='Solano'               49='Sonoma'            50='Stanislaus'
      51='Sutter'               52='Tehama'            53='Trinity'
      54='Tulare'               55='Tuolumne'          56='Ventura'
      57='Yolo'                 58='Yuba'              59='Missing'
	  60='Missing'
      99='Missing';
run;


*******************************************************************************************;
************************************* Begin program ***************************************;
*******************************************************************************************;

*** Set the Assignment end date into the future for ease of sorting ***;
/*
data assign;
   set cws.ASGNM_T;

   if END_DT = . then END_DT = '31DEC3000'd;

run;

*** Join the CASE and ASSIGN tables together ***;

proc sql;

   create table CASE_ASSIGN as
   select x.FKCHLD_CLT, 
          x.IDENTIFIER as caseid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.CASE_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, END_DT, END_TM;

quit;

*** Join the REFERRAL and ASSIGN tables together ***;

proc sql;

   create table REFE_ASSIGN as
   select x.IDENTIFIER as refid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.REFERL_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'R'
   order by refid, START_DT, END_DT, END_TM;

quit;
*/

/*proc sort data=cws.plc_hm_t out=plchm; by identifier; run;
data plchm; set plchm; plchmvar=1; run;

proc sort data=elf.ucb_fc         out=seons; by fkclient_t pe_s_dt oh_s_dt; run;*/
proc sort data=temp.siblings_&qt out=sibs(rename=(fkclient=fkclient_t)); by fkclient; run;

*******************************************************************************************;
************************************* Begin Macro   ***************************************;
*******************************************************************************************;

%macro sibs(month, outmonth, year, outfile); 

*******************************************************************************************;
***** Include         PIT program here - to adjust the PIT numbers - added 12/04/2002 *****;
***** * Changed to       PIT method on 9/22/2003.  This new method calculates PIT     *****;
*****   based on the month and year listed at beginning of program.                   *****;
***** * Changed to updated PIT on 5/16/07 - include actual code in program also.      *****;
*******************************************************************************************;

/*change*/
data seons_pit (rename= (pit_plc=fed));
set dwh.ucb_pit;
where agency = 1 and age le 20 and period_dt = "01&month.&year."d  /* '01JAN2019'D */ ;

*if 1800 < county < 2000 then county = 1900;
run;


*******************************************************************************************;
***** Adjust the family count - it should be based on PIT file only and it is now     *****;
***** based on the entire ELF file.                                                   *****;
*******************************************************************************************;

proc sort data=seons_pit; by fkclient_t; run;

data fredrick wilma pebbles;
   merge sibs(in=a)
         seons_pit(in=b);
   by fkclient_t;
   if fam_count ge 6 then fam_count=6;
   if a and b then output fredrick;
   else if a and not b then output wilma;
   else if b and not a then output pebbles;
run;

data fredrick2;
   set fredrick;
   sup_cty=County/100;
   /*change*/*zipno=zip_no;
   *drop zip_no;
run;

*** Remove ICPC children ***;

data fredrick3;
   length tfamid $12.;
   set fredrick2;
   sup_cty=cnty_spfcd/1;
   county = input(cnty_spfcd,best2.);
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||famid);
run;

proc sort data=fredrick3 nodupkey out=newfam(rename=(fkclient_t=fkclient)); by tfamid fkclient_t; run;

data newfam2(keep=tfamid count);
   set newfam;
   retain count;
   by tfamid;
   if first.tfamid then count=0;
   count=count+1;
   if last.tfamid then do;
      if count gt 6 then count=6;
      output;
   end;
run;

proc sort data=fredrick3; by tfamid;
proc sort data=newfam2; by tfamid;

data seons_pit2;
   merge fredrick3(rename=(fam_count=oldfamcount2)) newfam2;
   by tfamid;
run;

proc sort data=seons_pit2(rename=(count=fam_count));
by fkclient_t /* pe_s_dt oh_s_dt */;
run;

*******************************************************************************************;
***** Now that the correct sibling groups are included we can continue.               *****;
*******************************************************************************************;

/*change revised to use out of home placement table - only facility ID needed*/
/*proc sql;
   create table sibplchm as
   select a.*, b.*
   from seons_pit2 as a left join plchm as b
   on a.fkplc_hm_t=b.identifier ;
quit;*/
proc sql;
        create table sibplchm as
        select a.*, b.fkplc_hm_t
        from seons_pit2 as a left join cws.o_hm_plt as b
        on a.ohmpl_id = b.identifier;
        quit;

***** The file sibplchm contains all of the Siblings info and the PLCHM stuff *****;
***** The file sibplchm has 87,995 observations - one to one match.           *****;

/*changed*/proc sort data=sibplchm; by famid /*identifier oh_s_dt*/ fkplc_hm_t fkclient_t; run;

***** Sort the file by sibling id (famid), placement home (identifi), start   *****;
***** date (oh_s_dt) and child (fkclient).  I make several temporary variables*****;
***** to find if all of the children are placed together or not.              *****;

data multisib onlykids;
   length undupvar $50;
   set sibplchm;
   if fam_count lt 2 then output onlykids;
   else do;
      *undupvar=compress(compress(street_no)||compress(street_nm)||compress(zip_no)||compress(FACLTY_NM));
      /*change*/ *undupvar=identifier; undupvar=fkplc_hm_t;
         *** USe placement home ID instead of the address and name - no longer have access ;
      output multisib;
   end;
run;

***** The data step TRYIT creates counters based on the identifiers          *****;

proc sort data=multisib; by tfamid undupvar fkclient_t; run;

data tryit;
   length tempfk $10 counter 3.;
   set multisib;
   retain tempfk counter plccount tempdt;
   by tfamid undupvar fkclient_t;
   if first.tfamid then famcount=1;                             ***** Family counter for comparison            *****;
   if first.undupvar then do;
      counter=0; plccount=0; tempfk=' ' ; /* tempdt=oh_s_dt; */ ***** clear retained variables                 *****;
      if not last.undupvar then do;                             ***** if only one child dont do anything        *****;
         tempfk=fkclient_t;
         counter=1; plccount=1;tmp1=1;
      end;
   end;
   else do;
      if fkclient_t ne tempfk then do;            ***** only increase counter for diff. kids     *****;
         if fkclient_t ne lag(fkclient_t) then
            counter=counter+1;
      end;
   end;
   if last.undupvar then do;                    ***** On last record make variables as approp. *****;
      if fam_count le counter then siball=1;    ***** If counter=fam_count then all            *****;
      else if counter gt 1 then do;             ***** If counter ge 2 (more than one kid,      *****;
         sibsome=1; somenum=counter;            *****    but not all of the kids) then some    *****;
      end;
   end;
run;

***** Interested in all children in a family to have the same all/some listing *****;

proc sort data=tryit; by tfamid undupvar descending counter;

data finsib;
   retain temppa tempps tempsn ;
   set tryit;
   by tfamid undupvar ;
   if first.undupvar then do; temppa=siball; tempps=sibsome; tempsn=somenum; end;
   else do; siball=temppa; sibsome=tempps; somenum=tempsn; end;
   drop temppa tempps tempsn ;
run;

proc sort data=finsib; by tfamid descending counter;

data finsib;
   retain sibfams;
   set finsib;
   by tfamid ;
   if first.tfamid then sibfams=.;
   if first.tfamid and siball=1 then sibfams=1;
   else if first.tfamid and sibsome=1 then sibfams=1;
run;

***** Instead of unduplicating - roll any duplicate placements up *****;

proc sort data=finsib; by fkclient_T; run;

data finsib2;
   length identifi2 $10;
   retain identifi2 pe_s_dt2 pe_e_dt2 oh_s_dt2 oh_e_dt2;
   set finsib;
   by fkclient_t;
   if first.fkclient_T and last.fkclient_t then do;
      /*pe_s_dt2=.; pe_e_dt2=.; oh_s_dt2=.; oh_e_dt2=.;*/
      identifi2=' '; output;
   end;
   else if first.fkclient_t and not last.fkclient_t then do; /*change*/identifi2=fkplc_hm_t;
      /*pe_s_dt2=pe_s_dt; pe_e_dt2=pe_e_dt; oh_s_dt2=oh_s_dt; oh_e_dt2=oh_e_dt;*/
   end;
   else if last.fkclient_t and not first.fkclient_t then output;
run;

***** Examine the number of sibling all/some categories compared to total *****;
***** but by the number of siblings in the system.                        *****;

proc summary data=finsib2;
   class fam_count;
   var siball sibsome sibfams somenum;
   output out=summres3 sum=;
run;

data summres3;
   set summres3;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** Put the sibfams variable on the only kids who have siblings with some placed together *****;

proc sort data=onlykids nodupkey; by fkclient_t; run;

***** Bring the files together *****;

data finale;
   set finsib2(in=a)
       onlykids(in=b);

   /* Cut Nonrelative Placement */
   where FED not in (2, 9.2, 99);

   if siball ne 1 then siball=0;
   if sibsome ne 1 then sibsome=0;
   totplc=sum(siball,sibsome);
   if sup_cty lt 1 or sup_cty gt 58 then sup_cty=59;
run;

proc summary data=finale;
   class fam_count;
   var siball sibsome somenum;
   output out=summres4 sum=;
run;

data summres4;
   set summres4;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** List the number of Siblings by County *****;
***** First use valid county codes and other*****;

proc summary data=finale;
   class sup_cty fam_count;
   var siball sibsome somenum;
   output out=summres5 sum=;
run;

***** There will be two total lines - one for all kids and one for multi-siblings *****;

proc summary data=finale;
   class sup_cty fam_count;
   where fam_count gt 1;
   var siball sibsome somenum;
   output out=summres5c sum=;
run;

proc sort data=summres5c; by fam_count; where _type_ in (0,2); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data summres5b;
   length cntyname $27 newgvr $6;
   set summres5 summres5c(in=b);
   if b and sup_cty=. then sup_cty=0;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=summres5b; by newgvr fam_count; run;

***** The dataset siblingvars only has the matching variable and the flags. *****;

data pebbles;
   length tfamid $12.;
   set pebbles;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||fkclient_t);

run;

proc sort data=pebbles out=noelf; by fkclient_t; run;
proc sort data=noelf; by tfamid;
proc sort data=finale out=tf(keep=tfamid sibfams sup_cty); by tfamid; where sibfams=1; run;
proc sort data=tf nodupkey; by tfamid; run;

data noelf2;
   merge noelf(in=a) tf;
   by tfamid;
   if a;
run;

proc freq data=noelf2; table sup_cty /list missing; run;
data siblingvars;
   set finale noelf2;
   if siball=. then siball=0;
   if totplc=. then totplc=0;
   if sibfams=. then sibfams=0;
run;

*******************************************************************************************;
***** Totals by county                                                                *****;
*******************************************************************************************;

**************************************************************************************;
*****                                                                            *****;
***** Group the facility types into categories based on the following list:      *****;
*****       1='Pre-Adopt'                                                        *****;              
*****       2='Kin'                                                              *****;
*****       3='Foster'                                                           *****;
*****   3.1 4='FFA'                                                              *****;           
***** 3.5 5 9='Court Specified Home'                                             *****;
*****     9.2='Tribally Specified Home'                                          *****;
*****  4 6 10='Group'                                                            *****;
*****  5 7 11='Shelter'                                                          *****;          
***** 6 11 15='Guardian'                                                         *****; 
*****   12 16='Guardian-Other'                                                   *****;
*****      13='All Others' - including: 5.1 8 13=Non-FC, 6.1 99=Missing,         *****;
*****       7 13 17=Runaway, 8 14 18=Trial Home Visit, 9 15 19=SILP              *****;
*****      20=Transitional Housing 29=Other(?),                                  *****;
*****       10=ILSP,  12=Other-other                                             *****;
**************************************************************************************;

data sibkin;
   set siblingvars;
   if fed in (2,3,4,9,9.2,10,11,15,16) then tempfed=fed; 
   else tempfed=30;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where fam_count gt 1 and agency=1;
   var siball sibsome somenum;
   output out=fulld sum=;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where agency=1;
   var siball sibsome somenum;
   output out=fulle sum=;
run;

proc sort data=fulld; by fed fam_count; where _type_ in (0,2,4,6); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data fullf;
   length cntyname $27 newgvr $6;
   set fulle fulld(in=b);
   if b and sup_cty =. then sup_cty =0;
   if b and fam_count=. then fam_count=0;
   if not b and fam_count=. then fam_count=-1;
   pctall=siball/_freq_  *100 ;  pctsome=sibsome/_freq_ *100 ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_ * 100;
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=fullf; by sup_cty fed fam_count; run;

*******************************************************************************************;
*****  Create a filler table that has one record per county for the summary and       *****;
*****  one record per county per family size for the other reports.                   *****;
*******************************************************************************************;
data fullfb;
   set fullf;
   if sup_cty=. then sup_cty=0;
run;

proc sort data=fullfb; by sup_cty fed fam_count; run;

data template;
   do sup_cty=0 to 59;
      fed=.;    do fam_count=-1 to 6; output; end;
      fed=1;    do fam_count=-1 to 6; output; end;
      fed=2;    do fam_count=-1 to 6; output; end;
      fed=3;    do fam_count=-1 to 6; output; end;
      fed=4;    do fam_count=-1 to 6; output; end;
      fed=9;    do fam_count=-1 to 6; output; end;
      fed=9.2;  do fam_count=-1 to 6; output; end;
      fed=10;   do fam_count=-1 to 6; output; end;
      fed=11;   do fam_count=-1 to 6; output; end;
      fed=13;   do fam_count=-1 to 6; output; end;
 /*   fed=14;   do fam_count=-1 to 6; output; end;  */
      fed=15;   do fam_count=-1 to 6; output; end;
      fed=16;   do fam_count=-1 to 6; output; end;
      fed=17;   do fam_count=-1 to 6; output; end;
      fed=18;   do fam_count=-1 to 6; output; end;
      fed=19;   do fam_count=-1 to 6; output; end;
      fed=20;   do fam_count=-1 to 6; output; end;
      fed=29;   do fam_count=-1 to 6; output; end;
      fed=99;   do fam_count=-1 to 6; output; end;
   end;
run;

data fullg;
   length fedname $20;
   merge fullfb template;
   by sup_cty fed fam_count;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if first.fed then do;
      fedname=put(fed,afcars.);
      siball=.; pctall=.; sibsome=.; pctsome=.;  totplc=.; totpct=.;
   end;
   else do;
      fedname=' ';
	  if _freq_=. then _freq_=0;
	  if siball=. then siball=0;
	  if pctall=. then pctall=0.0;
	  if sibsome=. then sibsome=0;
	  if pctsome=. then pctsome=0.0;
	  if totplc=. then totplc=0;
	  if totpct=. then totpct=0.0;
   end;
run;

*******************************************************************************************;
*****  Summary tables                                                                 *****;
*******************************************************************************************;

%macro summary;

data sum_&outfile.(drop=fedname cntyname newgvr FED fam_count pqtr yr); 
   set fullg;
   where fam_count=0 and fed=.; 
   if cntyname=:'State' then cntyname='California';

pqtr = qtr("01&month.&year."d);
yr = year("01&month.&year."d);

PERIOD_DT = yyq(yr,pqtr);

rename
_FREQ_ = COUNT
sup_cty = COUNTY;

AGE_GRP = 2;
P_TYPE = 2;

run;

proc append base=local.siblings_nr2 data = sum_&outfile.;

%mend summary;
%summary;
%mend sibs;

%include time_period;

/*
%sibs(jan, January, 2025, jan2025);

%sibs(oct, October, 2024, oct2024);
%sibs(jul, July,    2024, jul2024);
%sibs(apr, April,   2024, apr2024);
%sibs(jan, January, 2024, jan2024);

%sibs(oct, October, 2023, oct2023);
%sibs(jul, July,    2023, jul2023);
%sibs(apr, April,   2023, apr2023);
%sibs(jan, January, 2023, jan2023);

%sibs(oct, October, 2022, oct2022);
%sibs(jul, July,    2022, jul2022);
%sibs(apr, April,   2022, apr2022);
%sibs(jan, January, 2022, jan2022);

%sibs(oct, October, 2021, oct2021);
%sibs(jul, July,    2021, jul2021);
%sibs(apr, April,   2021, apr2021);
%sibs(jan, January, 2021, jan2021);

%sibs(oct, October, 2020, oct2020);
%sibs(jul, July,    2020, jul2020);
%sibs(apr, April,   2020, apr2020);
%sibs(jan, January, 2020, jan2020);

%sibs(oct, October, 2019, oct2019);
%sibs(jul, July,    2019, jul2019);
%sibs(apr, April,   2019, apr2019);
%sibs(jan, January, 2019, jan2019);

%sibs(oct, October, 2018, oct2018);
%sibs(jul, July,    2018, jul2018);
%sibs(apr, April,   2018, apr2018);
%sibs(jan, January, 2018, jan2018);

%sibs(oct, October, 2017, oct2017);
%sibs(jul, July,    2017, jul2017);
%sibs(apr, April,   2017, apr2017);
%sibs(jan, January, 2017, jan2017);

%sibs(oct, October, 2016, oct2016);
%sibs(jul, July,    2016, jul2016);
%sibs(apr, April,   2016, apr2016);
%sibs(jan, January, 2016, jan2016);

%sibs(oct, October, 2015, oct2015);
%sibs(jul, July,    2015, jul2015);
%sibs(apr, April,   2015, apr2015);
%sibs(jan, January, 2015, jan2015);

%sibs(oct, October, 2014, oct2014);
%sibs(jul, July,    2014, jul2014);
%sibs(apr, April,   2014, apr2014);
%sibs(jan, January, 2014, jan2014);

%sibs(oct, October, 2013, oct2013);
%sibs(jul, July,    2013, jul2013);
%sibs(apr, April,   2013, apr2013);
%sibs(jan, January, 2013, jan2013);

%sibs(oct, October, 2012, oct2012);
%sibs(jul, July,    2012, jul2012);
%sibs(apr, April,   2012, apr2012);
%sibs(jan, January, 2012, jan2012);

%sibs(oct, October, 2011, oct2011);
%sibs(jul, July,    2011, jul2011);
%sibs(apr, April,   2011, apr2011);
%sibs(jan, January, 2011, jan2011);

%sibs(oct, October, 2010, oct2010);
%sibs(jul, July,    2010, jul2010);
%sibs(apr, April,   2010, apr2010);
%sibs(jan, January, 2010, jan2010);

%sibs(oct, October, 2009, oct2009);
%sibs(jul, July,    2009, jul2009);
%sibs(apr, April,   2009, apr2009);
%sibs(jan, January, 2009, jan2009);

%sibs(oct, October, 2008, oct2008);
%sibs(jul, July,    2008, jul2008);
%sibs(apr, April,   2008, apr2008);
%sibs(jan, January, 2008, jan2008);
*/
***************************************************************************;
**                                                                       **;
** SIBLING_PIT_FILES.sas                                                 **;
**                                                                       **;
** Copyright (c) 2006 Center for Social Services Research,               **;
** University of California at Berkeley. All rights reserved.            **;
** Center for Social Services Research - Univ of California at Berkeley  **;
** CWS/CMS Reporting:  Sibling Analysis                                  **;
**                                                                       **;
** This program takes a PIT file - created using a macro embedded in the **;
** program and the Sibling ID file and combines them to specify whether  **;
** siblings are placed together or not.                                  **;
**                                                                       **;
** FOR:  Barbara Needell          BY: Terry Shaw                         **;
**                                                                       **;
** This program was updated on 12/04/2002 to include the new PIT code    **;
**    (changes in ILSP and Limbo kids) and to incorporate the Placement  **;
**    /Ethnicity/and Matrix reports into a single program.               **;
** This program was updated on 9/22/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 10/9/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 9/22/2004 to enclose the program in macro **;
**    code to facilitate running multiple time periods.                  **;
** This program was updated on 3/14/2005 to change the placement home    **;
**    matching process to use IDENTIFIER rather than the created place-  **;
**    home address indicator because of confidentiality.                 **;
** This program was updated on 1/30/2005 to change the macro calling the **;
**    point in time code to use the most recent version of that code.    **;
** This program was updated on 9/26/2006 to update the way that siblings **;
**    are categorized.  Siblings are now calculated at the county level  **;
**    rather than the state level because counties do not 'see' siblings **;
**    outside of their county boundaries.                                **;
**                                                                       **;
** The following files are created:                                      **;
**     SIBf_00_xxx200y_s.xml   (Summary file)                            **;
**     SIBf_P0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_E0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_00_xxx200y_0.xml                                             **;
**                                                                       **;
**  2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group   **;
***************************************************************************;

option nocenter missing=' ';

filename time_period "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/PRODUCTS/SIBLINGS/time_period.sas";
libname userstor "/opt/sas8.2/ud_templates" ;
ods path userstor.templat(read) sashelp.tmplmst(read) ;

proc format;
   picture ntemp (round) low-<0=' '     0-<5='000,009' 5-high='000,009';
   picture dtemp (round) low-<0=' '     0.0-high='009.9';
   value vsibs  .=' '           1='1'          2='2'
                3='3'           4='4'          5='5'
                6='6+'         -1='All Children'          0='2+ Children Total*';
   value veth  .='Total'           1='Black'            2='White'
               3='Latino'        4='Asian/PI'         5='Nat. Amer.'
			   6='Missing'         0='Total';
   value afcars 1='Pre-Adopt'     
                2='Relative/NREFM'               
                3='Foster'
                4='FFA'           
                9='Court Specified Home'   
               10='Group'
               11='Shelter'        
               13='Non-FC'            
         /*    14='Guardian'  */
               15='Guardian - Dependent'    
               16='Guardian - Non-Dependent'
               17='Runaway'      
               18='Trial Home Visit' 
               19='SILP'
               20='Transitional Housing'
               29='Other'
               99='Missing'       
                .='Total' ;
   value vcnty
      00='California'           01='Alameda'           02='Alpine'
      03='Amador'               04='Butte'             05='Calaveras'
      06='Colusa'               07='Contra Costa'      08='Del Norte'
      09='El Dorado'            10='Fresno'            11='Glenn'
      12='Humboldt'             13='Imperial'          14='Inyo'
      15='Kern'                 16='Kings'             17='Lake'
      18='Lassen'               19='Los Angeles'       20='Madera'
      21='Marin'                22='Mariposa'          23='Mendocino'
      24='Merced'               25='Modoc'             26='Mono'
      27='Monterey'             28='Napa'              29='Nevada'
      30='Orange'               31='Placer'            32='Plumas'
      33='Riverside'            34='Sacramento'        35='San Benito'
      36='San Bernardino'       37='San Diego'         38='San Francisco'
      39='San Joaquin'          40='San Luis Obispo'   41='San Mateo'
      42='Santa Barbara'        43='Santa Clara'       44='Santa Cruz'
      45='Shasta'               46='Sierra'            47='Siskiyou'
      48='Solano'               49='Sonoma'            50='Stanislaus'
      51='Sutter'               52='Tehama'            53='Trinity'
      54='Tulare'               55='Tuolumne'          56='Ventura'
      57='Yolo'                 58='Yuba'              59='Missing'
	  60='Missing'
      99='Missing';
run;


*******************************************************************************************;
************************************* Begin program ***************************************;
*******************************************************************************************;

*** Set the Assignment end date into the future for ease of sorting ***;
/*
data assign;
   set cws.ASGNM_T;

   if END_DT = . then END_DT = '31DEC3000'd;

run;

*** Join the CASE and ASSIGN tables together ***;

proc sql;

   create table CASE_ASSIGN as
   select x.FKCHLD_CLT, 
          x.IDENTIFIER as caseid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.CASE_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, END_DT, END_TM;

quit;

*** Join the REFERRAL and ASSIGN tables together ***;

proc sql;

   create table REFE_ASSIGN as
   select x.IDENTIFIER as refid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.REFERL_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'R'
   order by refid, START_DT, END_DT, END_TM;

quit;
*/


/*proc sort data=cws.plc_hm_t out=plchm; by identifier; run;
data plchm; set plchm; plchmvar=1; run;

proc sort data=elf.ucb_fc         out=seons; by fkclient_t pe_s_dt oh_s_dt; run;*/
proc sort data=temp.siblings_&qt out=sibs(rename=(fkclient=fkclient_t)); by fkclient; run;

*******************************************************************************************;
************************************* Begin Macro   ***************************************;
*******************************************************************************************;

%macro sibs(month, outmonth, year, outfile); 

*******************************************************************************************;
***** Include         PIT program here - to adjust the PIT numbers - added 12/04/2002 *****;
***** * Changed to       PIT method on 9/22/2003.  This new method calculates PIT     *****;
*****   based on the month and year listed at beginning of program.                   *****;
***** * Changed to updated PIT on 5/16/07 - include actual code in program also.      *****;
*******************************************************************************************;

/*change*/
data seons_pit (rename= (pit_plc=FED));
set dwh.ucb_pit;
where agency = 1 and age le 17 and period_dt = "01&month.&year."d  /* '01JAN2019'D */ ;

*if 1800 < county < 2000 then county = 1900;
run;

*******************************************************************************************;
***** Adjust the family count - it should be based on PIT file only and it is now     *****;
***** based on the entire ELF file.                                                   *****;
*******************************************************************************************;

proc sort data=seons_pit; by fkclient_t; run;

data fredrick wilma pebbles;
   merge sibs(in=a)
         seons_pit(in=b);
   by fkclient_t;
   if fam_count ge 6 then fam_count=6;
   if a and b then output fredrick;
   else if a and not b then output wilma;
   else if b and not a then output pebbles;
run;

data fredrick2;
   set fredrick;
   sup_cty=cnty_spfcd/1;
   county = input(cnty_spfcd,best2.);
   /*change*/*zipno=zip_no;
   *drop zip_no;
run;

*** Remove ICPC children ***;

data fredrick3;
   length tfamid $12.;
   set fredrick2;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||famid);
run;

proc sort data=fredrick3 nodupkey out=newfam(rename=(fkclient_t=fkclient)); by tfamid fkclient_t; run;

data newfam2(keep=tfamid count);
   set newfam;
   retain count;
   by tfamid;
   if first.tfamid then count=0;
   count=count+1;
   if last.tfamid then do;
      if count gt 6 then count=6;
      output;
   end;
run;

proc sort data=fredrick3; by tfamid;
proc sort data=newfam2; by tfamid;

data seons_pit2;
   merge fredrick3(rename=(fam_count=oldfamcount2)) newfam2;
   by tfamid;
run;

proc sort data=seons_pit2(rename=(count=fam_count));
by fkclient_t; * pe_s_dt oh_s_dt;/*change UCB_PIT does not include pe_s_dt or oh_s_dt as point in time only*/
run;

*******************************************************************************************;
***** Now that the correct sibling groups are included we can continue.               *****;
*******************************************************************************************;

/*change revised to use out of home placement table - only facility ID needed*/
/*proc sql;
   create table sibplchm as
   select a.*, b.*
   from seons_pit2 as a left join plchm as b
   on a.fkplc_hm_t=b.identifier ;
quit;*/
proc sql;
        create table sibplchm as
        select a.*, b.fkplc_hm_t
        from seons_pit2 as a left join cws.o_hm_plt as b
        on a.ohmpl_id = b.identifier;
        quit;

***** The file sibplchm contains all of the Siblings info and the PLCHM stuff *****;
***** The file sibplchm has 87,995 observations - one to one match.           *****;

/*changed*/proc sort data=sibplchm; by famid /*identifier oh_s_dt*/ fkplc_hm_t fkclient_t; run;

***** Sort the file by sibling id (famid), placement home (identifi), start   *****;
***** date (oh_s_dt) and child (fkclient).  I make several temporary variables*****;
***** to find if all of the children are placed together or not.              *****;

data multisib onlykids;
   length undupvar $50;
   set sibplchm;
   if fam_count lt 2 then output onlykids;
   else do;
      *undupvar=compress(compress(street_no)||compress(street_nm)||compress(zip_no)||compress(FACLTY_NM));
      /*change*/ *undupvar=identifier; undupvar=fkplc_hm_t;
         *** USe placement home ID instead of the address and name - no longer have access ;
      output multisib;
   end;
run;

***** The data step TRYIT creates counters based on the identifiers          *****;

proc sort data=multisib; by tfamid undupvar fkclient_t; run;

data tryit;
   length tempfk $10 counter 3.;
   set multisib;
   retain tempfk counter plccount /* tempdt */;
   by tfamid undupvar fkclient_t;
   if first.tfamid then famcount=1;                              ***** Family counter for comparison            *****;
   if first.undupvar then do;
      counter=0; plccount=0; tempfk=' ' ; /* tempdt=oh_s_dt; */  ***** clear retained variables                 *****;
      if not last.undupvar then do;                              ***** if only one child dont do anything        *****;
         tempfk=fkclient_t;
         counter=1; plccount=1;tmp1=1;
      end;
   end;
   else do;
      if fkclient_t ne tempfk then do;            ***** only increase counter for diff. kids     *****;
         if fkclient_t ne lag(fkclient_t) then
            counter=counter+1;
      end;
   end;
   if last.undupvar then do;                    ***** On last record make variables as approp. *****;
      if fam_count le counter then siball=1;    ***** If counter=fam_count then all            *****;
      else if counter gt 1 then do;             ***** If counter ge 2 (more than one kid,      *****;
         sibsome=1; somenum=counter;            *****    but not all of the kids) then some    *****;
      end;
   end;
run;

***** Interested in all children in a family to have the same all/some listing *****;

proc sort data=tryit; by tfamid undupvar descending counter;

data finsib;
   retain temppa tempps tempsn ;
   set tryit;
   by tfamid undupvar ;
   if first.undupvar then do; temppa=siball; tempps=sibsome; tempsn=somenum; end;
   else do; siball=temppa; sibsome=tempps; somenum=tempsn; end;
   drop temppa tempps tempsn ;
run;

proc sort data=finsib; by tfamid descending counter;

data finsib;
   retain sibfams;
   set finsib;
   by tfamid ;
   if first.tfamid then sibfams=.;
   if first.tfamid and siball=1 then sibfams=1;
   else if first.tfamid and sibsome=1 then sibfams=1;
run;

***** Instead of unduplicating - roll any duplicate placements up *****;

proc sort data=finsib; by fkclient_T; run;

data finsib2;
   length identifi2 $10;
   retain identifi2 /* pe_s_dt2 pe_e_dt2 oh_s_dt2 oh_e_dt2 */;
   set finsib;
   by fkclient_t;
   if first.fkclient_T and last.fkclient_t then do;
   /* pe_s_dt2=.; pe_e_dt2=.; oh_s_dt2=.; oh_e_dt2=.; */
      identifi2=' '; output;
   end;
   else if first.fkclient_t and not last.fkclient_t then do; /*change*/identifi2=fkplc_hm_t;
      /*pe_s_dt2=pe_s_dt; pe_e_dt2=pe_e_dt; oh_s_dt2=oh_s_dt; oh_e_dt2=oh_e_dt;*/
   end;
   else if last.fkclient_t and not first.fkclient_t then output;
run;

***** Examine the number of sibling all/some categories compared to total *****;
***** but by the number of siblings in the system.                        *****;

proc summary data=finsib2;
   class fam_count;
   var siball sibsome sibfams somenum;
   output out=summres3 sum=;
run;

data summres3;
   set summres3;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** Put the sibfams variable on the only kids who have siblings with some placed together *****;

proc sort data=onlykids nodupkey; by fkclient_t; run;

***** Bring the files together *****;

data finale;
   set finsib2(in=a)
       onlykids(in=b);

/* Cut Relative Placement */
   where FED in (2, 9.2);

   if siball ne 1 then siball=0;
   if sibsome ne 1 then sibsome=0;
   totplc=sum(siball,sibsome);
   if sup_cty lt 1 or sup_cty gt 58 then sup_cty=59;
run;

proc summary data=finale;
   class fam_count;
   var siball sibsome somenum;
   output out=summres4 sum=;
run;

data summres4;
   set summres4;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** List the number of Siblings by County *****;
***** First use valid county codes and other*****;

proc summary data=finale;
   class sup_cty fam_count;
   var siball sibsome somenum;
   output out=summres5 sum=;
run;

***** There will be two total lines - one for all kids and one for multi-siblings *****;

proc summary data=finale;
   class sup_cty fam_count;
   where fam_count gt 1;
   var siball sibsome somenum;
   output out=summres5c sum=;
run;

proc sort data=summres5c; by fam_count; where _type_ in (0,2); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data summres5b;
   length cntyname $27 newgvr $6;
   set summres5 summres5c(in=b);
   if b and sup_cty=. then sup_cty=0;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=summres5b; by newgvr fam_count; run;

***** The dataset siblingvars only has the matching variable and the flags. *****;

data pebbles;
   length tfamid $12.;
   set pebbles;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||fkclient_t);

run;

proc sort data=pebbles out=noelf; by fkclient_t; run;
proc sort data=noelf; by tfamid;
proc sort data=finale out=tf(keep=tfamid sibfams sup_cty); by tfamid; where sibfams=1; run;
proc sort data=tf nodupkey; by tfamid; run;

data noelf2;
   merge noelf(in=a) tf;
   by tfamid;
   if a;
run;

proc freq data=noelf2; table sup_cty /list missing; run;
data siblingvars;
   set finale noelf2;
   if siball=. then siball=0;
   if totplc=. then totplc=0;
   if sibfams=. then sibfams=0;
run;

*******************************************************************************************;
***** Totals by county                                                                *****;
*******************************************************************************************;

**************************************************************************************;
*****                                                                            *****;
***** Group the facility types into categories based on the following list:      *****;
*****       1='Pre-Adopt'                                                        *****;              
*****       2='Kin'                                                              *****;
*****       3='Foster'                                                           *****;
*****   3.1 4='FFA'                                                              *****;           
***** 3.5 5 9='Court Specified Home'                                             *****;
*****     9.2='Tribally Specified Home'                                          *****;
*****  4 6 10='Group'                                                            *****;
*****  5 7 11='Shelter'                                                          *****;          
***** 6 11 15='Guardian'                                                         *****; 
*****   12 16='Guardian-Other'                                                   *****;
*****      13='All Others' - including: 5.1 8 13=Non-FC, 6.1 99=Missing,         *****;
*****       7 13 17=Runaway, 8 14 18=Trial Home Visit, 9 15 19=SILP              *****;
*****      20=Transitional Housing 29=Other(?),                                  *****;
*****       10=ILSP,  12=Other-other                                             *****;
**************************************************************************************;

data sibkin;
   set siblingvars;
   if fed in (2,3,4,9,9.2,10,11,15,16) then tempfed=fed; 
   else tempfed=30;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where fam_count gt 1 and agency=1;
   var siball sibsome somenum;
   output out=fulld sum=;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where agency=1;
   var siball sibsome somenum;
   output out=fulle sum=;
run;

proc sort data=fulld; by fed fam_count; where _type_ in (0,2,4,6); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data fullf;
   length cntyname $27 newgvr $6;
   set fulle fulld(in=b);
   if b and sup_cty =. then sup_cty =0;
   if b and fam_count=. then fam_count=0;
   if not b and fam_count=. then fam_count=-1;
   pctall=siball/_freq_  *100 ;  pctsome=sibsome/_freq_ *100 ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_ * 100;
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=fullf; by sup_cty fed fam_count; run;

*******************************************************************************************;
*****  Create a filler table that has one record per county for the summary and       *****;
*****  one record per county per family size for the other reports.                   *****;
*******************************************************************************************;
data fullfb;
   set fullf;
   if sup_cty=. then sup_cty=0;
run;

proc sort data=fullfb; by sup_cty fed fam_count; run;

data template;
   do sup_cty=0 to 59;
      fed=.;    do fam_count=-1 to 6; output; end;
      fed=1;    do fam_count=-1 to 6; output; end;
      fed=2;    do fam_count=-1 to 6; output; end;
      fed=3;    do fam_count=-1 to 6; output; end;
      fed=4;    do fam_count=-1 to 6; output; end;
      fed=9;    do fam_count=-1 to 6; output; end;
      fed=9.2;  do fam_count=-1 to 6; output; end;
      fed=10;   do fam_count=-1 to 6; output; end;
      fed=11;   do fam_count=-1 to 6; output; end;
      fed=13;   do fam_count=-1 to 6; output; end;
 /*   fed=14;   do fam_count=-1 to 6; output; end;  */
      fed=15;   do fam_count=-1 to 6; output; end;
      fed=16;   do fam_count=-1 to 6; output; end;
      fed=17;   do fam_count=-1 to 6; output; end;
      fed=18;   do fam_count=-1 to 6; output; end;
      fed=19;   do fam_count=-1 to 6; output; end;
      fed=20;   do fam_count=-1 to 6; output; end;
      fed=29;   do fam_count=-1 to 6; output; end;
      fed=99;   do fam_count=-1 to 6; output; end;
   end;
run;

data fullg;
   length fedname $20;
   merge fullfb template;
   by sup_cty fed fam_count;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if first.fed then do;
      fedname=put(fed,afcars.);
      siball=.; pctall=.; sibsome=.; pctsome=.;  totplc=.; totpct=.;
   end;
   else do;
      fedname=' ';
	  if _freq_=. then _freq_=0;
	  if siball=. then siball=0;
	  if pctall=. then pctall=0.0;
	  if sibsome=. then sibsome=0;
	  if pctsome=. then pctsome=0.0;
	  if totplc=. then totplc=0;
	  if totpct=. then totpct=0.0;
   end;
run;

*******************************************************************************************;
*****  Summary tables                                                                 *****;
*******************************************************************************************;

%macro summary;

data sum_&outfile.(drop=fedname cntyname newgvr FED fam_count pqtr yr); 
   set fullg;
   where fam_count=0 and fed=.; 
   if cntyname=:'State' then cntyname='California';

pqtr = qtr("01&month.&year."d);
yr = year("01&month.&year."d);

PERIOD_DT = yyq(yr,pqtr);

rename
_FREQ_ = COUNT
sup_cty = COUNTY;

AGE_GRP = 1;
P_TYPE = 1;

run;

proc append base=local.siblings_re1 data = sum_&outfile.;

%mend summary;
%summary;
%mend sibs;

%include time_period;

/*
%sibs(jan, January, 2025, jan2025);

%sibs(oct, October, 2024, oct2024);
%sibs(jul, July,    2024, jul2024);
%sibs(apr, April,   2024, apr2024);
%sibs(jan, January, 2024, jan2024);

%sibs(oct, October, 2023, oct2023);
%sibs(jul, July,    2023, jul2023);
%sibs(apr, April,   2023, apr2023);
%sibs(jan, January, 2023, jan2023);

%sibs(oct, October, 2022, oct2022);
%sibs(jul, July,    2022, jul2022);
%sibs(apr, April,   2022, apr2022);
%sibs(jan, January, 2022, jan2022);

%sibs(oct, October, 2021, oct2021);
%sibs(jul, July,    2021, jul2021);
%sibs(apr, April,   2021, apr2021);
%sibs(jan, January, 2021, jan2021);

%sibs(oct, October, 2020, oct2020);
%sibs(jul, July,    2020, jul2020);
%sibs(apr, April,   2020, apr2020);
%sibs(jan, January, 2020, jan2020);

%sibs(oct, October, 2019, oct2019);
%sibs(jul, July,    2019, jul2019);
%sibs(apr, April,   2019, apr2019);
%sibs(jan, January, 2019, jan2019);

%sibs(oct, October, 2018, oct2018);
%sibs(jul, July,    2018, jul2018);
%sibs(apr, April,   2018, apr2018);
%sibs(jan, January, 2018, jan2018);

%sibs(oct, October, 2017, oct2017);
%sibs(jul, July,    2017, jul2017);
%sibs(apr, April,   2017, apr2017);
%sibs(jan, January, 2017, jan2017);

%sibs(oct, October, 2016, oct2016);
%sibs(jul, July,    2016, jul2016);
%sibs(apr, April,   2016, apr2016);
%sibs(jan, January, 2016, jan2016);

%sibs(oct, October, 2015, oct2015);
%sibs(jul, July,    2015, jul2015);
%sibs(apr, April,   2015, apr2015);
%sibs(jan, January, 2015, jan2015);

%sibs(oct, October, 2014, oct2014);
%sibs(jul, July,    2014, jul2014);
%sibs(apr, April,   2014, apr2014);
%sibs(jan, January, 2014, jan2014);

%sibs(oct, October, 2013, oct2013);
%sibs(jul, July,    2013, jul2013);
%sibs(apr, April,   2013, apr2013);
%sibs(jan, January, 2013, jan2013);

%sibs(oct, October, 2012, oct2012);
%sibs(jul, July,    2012, jul2012);
%sibs(apr, April,   2012, apr2012);
%sibs(jan, January, 2012, jan2012);

%sibs(oct, October, 2011, oct2011);
%sibs(jul, July,    2011, jul2011);
%sibs(apr, April,   2011, apr2011);
%sibs(jan, January, 2011, jan2011);

%sibs(oct, October, 2010, oct2010);
%sibs(jul, July,    2010, jul2010);
%sibs(apr, April,   2010, apr2010);
%sibs(jan, January, 2010, jan2010);

%sibs(oct, October, 2009, oct2009);
%sibs(jul, July,    2009, jul2009);
%sibs(apr, April,   2009, apr2009);
%sibs(jan, January, 2009, jan2009);

%sibs(oct, October, 2008, oct2008);
%sibs(jul, July,    2008, jul2008);
%sibs(apr, April,   2008, apr2008);
%sibs(jan, January, 2008, jan2008);
*/
***************************************************************************;
**                                                                       **;
** SIBLING_PIT_FILES.sas                                                 **;
**                                                                       **;
** Copyright (c) 2006 Center for Social Services Research,               **;
** University of California at Berkeley. All rights reserved.            **;
** Center for Social Services Research - Univ of California at Berkeley  **;
** CWS/CMS Reporting:  Sibling Analysis                                  **;
**                                                                       **;
** This program takes a PIT file - created using a macro embedded in the **;
** program and the Sibling ID file and combines them to specify whether  **;
** siblings are placed together or not.                                  **;
**                                                                       **;
** FOR:  Barbara Needell          BY: Terry Shaw                         **;
**                                                                       **;
** This program was updated on 12/04/2002 to include the new PIT code    **;
**    (changes in ILSP and Limbo kids) and to incorporate the Placement  **;
**    /Ethnicity/and Matrix reports into a single program.               **;
** This program was updated on 9/22/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 10/9/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 9/22/2004 to enclose the program in macro **;
**    code to facilitate running multiple time periods.                  **;
** This program was updated on 3/14/2005 to change the placement home    **;
**    matching process to use IDENTIFIER rather than the created place-  **;
**    home address indicator because of confidentiality.                 **;
** This program was updated on 1/30/2005 to change the macro calling the **;
**    point in time code to use the most recent version of that code.    **;
** This program was updated on 9/26/2006 to update the way that siblings **;
**    are categorized.  Siblings are now calculated at the county level  **;
**    rather than the state level because counties do not 'see' siblings **;
**    outside of their county boundaries.                                **;
**                                                                       **;
** The following files are created:                                      **;
**     SIBf_00_xxx200y_s.xml   (Summary file)                            **;
**     SIBf_P0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_E0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_00_xxx200y_0.xml                                             **;
**                                                                       **;
**  2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group   **;
***************************************************************************;

option nocenter missing=' ';

filename time_period "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/PRODUCTS/SIBLINGS/time_period.sas";
libname userstor "/opt/sas8.2/ud_templates" ;
ods path userstor.templat(read) sashelp.tmplmst(read) ;

proc format;
   picture ntemp (round) low-<0=' '     0-<5='000,009' 5-high='000,009';
   picture dtemp (round) low-<0=' '     0.0-high='009.9';
   value vsibs  .=' '           1='1'          2='2'
                3='3'           4='4'          5='5'
                6='6+'         -1='All Children'          0='2+ Children Total*';
   value veth  .='Total'           1='Black'            2='White'
               3='Latino'        4='Asian/PI'         5='Nat. Amer.'
			   6='Missing'         0='Total';
   value afcars 1='Pre-Adopt'     
                2='Relative/NREFM'               
                3='Foster'
                4='FFA'           
                9='Court Specified Home'   
              9.2='Tribally Specified Home'
               10='Group'
               11='Shelter'        
               13='Non-FC'            
         /*    14='Guardian'  */
               15='Guardian - Dependent'    
               16='Guardian - Non-Dependent'
               17='Runaway'      
               18='Trial Home Visit' 
               19='SILP'
               20='Transitional Housing'
               29='Other'
               99='Missing'       
                .='Total' ;
   value vcnty
      00='California'           01='Alameda'           02='Alpine'
      03='Amador'               04='Butte'             05='Calaveras'
      06='Colusa'               07='Contra Costa'      08='Del Norte'
      09='El Dorado'            10='Fresno'            11='Glenn'
      12='Humboldt'             13='Imperial'          14='Inyo'
      15='Kern'                 16='Kings'             17='Lake'
      18='Lassen'               19='Los Angeles'       20='Madera'
      21='Marin'                22='Mariposa'          23='Mendocino'
      24='Merced'               25='Modoc'             26='Mono'
      27='Monterey'             28='Napa'              29='Nevada'
      30='Orange'               31='Placer'            32='Plumas'
      33='Riverside'            34='Sacramento'        35='San Benito'
      36='San Bernardino'       37='San Diego'         38='San Francisco'
      39='San Joaquin'          40='San Luis Obispo'   41='San Mateo'
      42='Santa Barbara'        43='Santa Clara'       44='Santa Cruz'
      45='Shasta'               46='Sierra'            47='Siskiyou'
      48='Solano'               49='Sonoma'            50='Stanislaus'
      51='Sutter'               52='Tehama'            53='Trinity'
      54='Tulare'               55='Tuolumne'          56='Ventura'
      57='Yolo'                 58='Yuba'              59='Missing'
	  60='Missing'
      99='Missing';
run;


*******************************************************************************************;
************************************* Begin program ***************************************;
*******************************************************************************************;

*** Set the Assignment end date into the future for ease of sorting ***;
/*
data assign;
   set cws.ASGNM_T;

   if END_DT = . then END_DT = '31DEC3000'd;

run;

*** Join the CASE and ASSIGN tables together ***;

proc sql;

   create table CASE_ASSIGN as
   select x.FKCHLD_CLT, 
          x.IDENTIFIER as caseid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.CASE_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, END_DT, END_TM;

quit;

*** Join the REFERRAL and ASSIGN tables together ***;

proc sql;

   create table REFE_ASSIGN as
   select x.IDENTIFIER as refid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.REFERL_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'R'
   order by refid, START_DT, END_DT, END_TM;

quit;
*/

/*proc sort data=cws.plc_hm_t out=plchm; by identifier; run;
data plchm; set plchm; plchmvar=1; run;

proc sort data=elf.ucb_fc         out=seons; by fkclient_t pe_s_dt oh_s_dt; run;*/
proc sort data=temp.siblings_&qt out=sibs(rename=(fkclient=fkclient_t)); by fkclient; run;

*******************************************************************************************;
************************************* Begin Macro   ***************************************;
*******************************************************************************************;

%macro sibs(month, outmonth, year, outfile); 

*******************************************************************************************;
***** Include         PIT program here - to adjust the PIT numbers - added 12/04/2002 *****;
***** * Changed to       PIT method on 9/22/2003.  This new method calculates PIT     *****;
*****   based on the month and year listed at beginning of program.                   *****;
***** * Changed to updated PIT on 5/16/07 - include actual code in program also.      *****;
*******************************************************************************************;

/*change*/
data seons_pit (rename= (pit_plc=fed));
set dwh.ucb_pit;
where agency = 1 and age le 20 and period_dt = "01&month.&year."d  /* '01JAN2019'D */ ;

*if 1800 < county < 2000 then county = 1900;
run;


*******************************************************************************************;
***** Adjust the family count - it should be based on PIT file only and it is now     *****;
***** based on the entire ELF file.                                                   *****;
*******************************************************************************************;

proc sort data=seons_pit; by fkclient_t; run;

data fredrick wilma pebbles;
   merge sibs(in=a)
         seons_pit(in=b);
   by fkclient_t;
   if fam_count ge 6 then fam_count=6;
   if a and b then output fredrick;
   else if a and not b then output wilma;
   else if b and not a then output pebbles;
run;

data fredrick2;
   set fredrick;
   sup_cty=cnty_spfcd/1;
   county = input(cnty_spfcd,best2.);
   /*change*/*zipno=zip_no;
   *drop zip_no;
run;

*** Remove ICPC children ***;

data fredrick3;
   length tfamid $12.;
   set fredrick2;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||famid);
run;

proc sort data=fredrick3 nodupkey out=newfam(rename=(fkclient_t=fkclient)); by tfamid fkclient_t; run;

data newfam2(keep=tfamid count);
   set newfam;
   retain count;
   by tfamid;
   if first.tfamid then count=0;
   count=count+1;
   if last.tfamid then do;
      if count gt 6 then count=6;
      output;
   end;
run;

proc sort data=fredrick3; by tfamid;
proc sort data=newfam2; by tfamid;

data seons_pit2;
   merge fredrick3(rename=(fam_count=oldfamcount2)) newfam2;
   by tfamid;
run;

proc sort data=seons_pit2(rename=(count=fam_count));
by fkclient_t /* pe_s_dt oh_s_dt */;
run;

*******************************************************************************************;
***** Now that the correct sibling groups are included we can continue.               *****;
*******************************************************************************************;

/*change revised to use out of home placement table - only facility ID needed*/
/*proc sql;
   create table sibplchm as
   select a.*, b.*
   from seons_pit2 as a left join plchm as b
   on a.fkplc_hm_t=b.identifier ;
quit;*/
proc sql;
        create table sibplchm as
        select a.*, b.fkplc_hm_t
        from seons_pit2 as a left join cws.o_hm_plt as b
        on a.ohmpl_id = b.identifier;
        quit;

***** The file sibplchm contains all of the Siblings info and the PLCHM stuff *****;
***** The file sibplchm has 87,995 observations - one to one match.           *****;

/*changed*/proc sort data=sibplchm; by famid /*identifier oh_s_dt*/ fkplc_hm_t fkclient_t; run;

***** Sort the file by sibling id (famid), placement home (identifi), start   *****;
***** date (oh_s_dt) and child (fkclient).  I make several temporary variables*****;
***** to find if all of the children are placed together or not.              *****;

data multisib onlykids;
   length undupvar $50;
   set sibplchm;
   if fam_count lt 2 then output onlykids;
   else do;
      *undupvar=compress(compress(street_no)||compress(street_nm)||compress(zip_no)||compress(FACLTY_NM));
      /*change*/ *undupvar=identifier; undupvar=fkplc_hm_t;
         *** USe placement home ID instead of the address and name - no longer have access ;
      output multisib;
   end;
run;

***** The data step TRYIT creates counters based on the identifiers          *****;

proc sort data=multisib; by tfamid undupvar fkclient_t; run;

data tryit;
   length tempfk $10 counter 3.;
   set multisib;
   retain tempfk counter plccount tempdt;
   by tfamid undupvar fkclient_t;
   if first.tfamid then famcount=1;                             ***** Family counter for comparison            *****;
   if first.undupvar then do;
      counter=0; plccount=0; tempfk=' ' ; /* tempdt=oh_s_dt; */ ***** clear retained variables                 *****;
      if not last.undupvar then do;                             ***** if only one child dont do anything        *****;
         tempfk=fkclient_t;
         counter=1; plccount=1;tmp1=1;
      end;
   end;
   else do;
      if fkclient_t ne tempfk then do;            ***** only increase counter for diff. kids     *****;
         if fkclient_t ne lag(fkclient_t) then
            counter=counter+1;
      end;
   end;
   if last.undupvar then do;                    ***** On last record make variables as approp. *****;
      if fam_count le counter then siball=1;    ***** If counter=fam_count then all            *****;
      else if counter gt 1 then do;             ***** If counter ge 2 (more than one kid,      *****;
         sibsome=1; somenum=counter;            *****    but not all of the kids) then some    *****;
      end;
   end;
run;

***** Interested in all children in a family to have the same all/some listing *****;

proc sort data=tryit; by tfamid undupvar descending counter;

data finsib;
   retain temppa tempps tempsn ;
   set tryit;
   by tfamid undupvar ;
   if first.undupvar then do; temppa=siball; tempps=sibsome; tempsn=somenum; end;
   else do; siball=temppa; sibsome=tempps; somenum=tempsn; end;
   drop temppa tempps tempsn ;
run;

proc sort data=finsib; by tfamid descending counter;

data finsib;
   retain sibfams;
   set finsib;
   by tfamid ;
   if first.tfamid then sibfams=.;
   if first.tfamid and siball=1 then sibfams=1;
   else if first.tfamid and sibsome=1 then sibfams=1;
run;

***** Instead of unduplicating - roll any duplicate placements up *****;

proc sort data=finsib; by fkclient_T; run;

data finsib2;
   length identifi2 $10;
   retain identifi2 pe_s_dt2 pe_e_dt2 oh_s_dt2 oh_e_dt2;
   set finsib;
   by fkclient_t;
   if first.fkclient_T and last.fkclient_t then do;
      /*pe_s_dt2=.; pe_e_dt2=.; oh_s_dt2=.; oh_e_dt2=.;*/
      identifi2=' '; output;
   end;
   else if first.fkclient_t and not last.fkclient_t then do; /*change*/identifi2=fkplc_hm_t;
      /*pe_s_dt2=pe_s_dt; pe_e_dt2=pe_e_dt; oh_s_dt2=oh_s_dt; oh_e_dt2=oh_e_dt;*/
   end;
   else if last.fkclient_t and not first.fkclient_t then output;
run;

***** Examine the number of sibling all/some categories compared to total *****;
***** but by the number of siblings in the system.                        *****;

proc summary data=finsib2;
   class fam_count;
   var siball sibsome sibfams somenum;
   output out=summres3 sum=;
run;

data summres3;
   set summres3;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** Put the sibfams variable on the only kids who have siblings with some placed together *****;

proc sort data=onlykids nodupkey; by fkclient_t; run;

***** Bring the files together *****;

data finale;
   set finsib2(in=a)
       onlykids(in=b);

/* Cut Relative Placement */
   where FED in (2, 9.2);

   if siball ne 1 then siball=0;
   if sibsome ne 1 then sibsome=0;
   totplc=sum(siball,sibsome);
   if sup_cty lt 1 or sup_cty gt 58 then sup_cty=59;
run;

proc summary data=finale;
   class fam_count;
   var siball sibsome somenum;
   output out=summres4 sum=;
run;

data summres4;
   set summres4;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** List the number of Siblings by County *****;
***** First use valid county codes and other*****;

proc summary data=finale;
   class sup_cty fam_count;
   var siball sibsome somenum;
   output out=summres5 sum=;
run;

***** There will be two total lines - one for all kids and one for multi-siblings *****;

proc summary data=finale;
   class sup_cty fam_count;
   where fam_count gt 1;
   var siball sibsome somenum;
   output out=summres5c sum=;
run;

proc sort data=summres5c; by fam_count; where _type_ in (0,2); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data summres5b;
   length cntyname $27 newgvr $6;
   set summres5 summres5c(in=b);
   if b and sup_cty=. then sup_cty=0;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=summres5b; by newgvr fam_count; run;

***** The dataset siblingvars only has the matching variable and the flags. *****;

data pebbles;
   length tfamid $12.;
   set pebbles;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||fkclient_t);

run;

proc sort data=pebbles out=noelf; by fkclient_t; run;
proc sort data=noelf; by tfamid;
proc sort data=finale out=tf(keep=tfamid sibfams sup_cty); by tfamid; where sibfams=1; run;
proc sort data=tf nodupkey; by tfamid; run;

data noelf2;
   merge noelf(in=a) tf;
   by tfamid;
   if a;
run;

proc freq data=noelf2; table sup_cty /list missing; run;
data siblingvars;
   set finale noelf2;
   if siball=. then siball=0;
   if totplc=. then totplc=0;
   if sibfams=. then sibfams=0;
run;

*******************************************************************************************;
***** Totals by county                                                                *****;
*******************************************************************************************;

**************************************************************************************;
*****                                                                            *****;
***** Group the facility types into categories based on the following list:      *****;
*****       1='Pre-Adopt'                                                        *****;              
*****       2='Kin'                                                              *****;
*****       3='Foster'                                                           *****;
*****   3.1 4='FFA'                                                              *****;           
***** 3.5 5 9='Court Specified Home'                                             *****;
*****     9.2='Tribally Specified Home'                                          *****;
*****  4 6 10='Group'                                                            *****;
*****  5 7 11='Shelter'                                                          *****;          
***** 6 11 15='Guardian'                                                         *****; 
*****   12 16='Guardian-Other'                                                   *****;
*****      13='All Others' - including: 5.1 8 13=Non-FC, 6.1 99=Missing,         *****;
*****       7 13 17=Runaway, 8 14 18=Trial Home Visit, 9 15 19=SILP              *****;
*****      20=Transitional Housing 29=Other(?),                                  *****;
*****       10=ILSP,  12=Other-other                                             *****;
**************************************************************************************;

data sibkin;
   set siblingvars;
   if fed in (2,3,4,9,9.2,10,11,15,16) then tempfed=fed; 
   else tempfed=30;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where fam_count gt 1 and agency=1;
   var siball sibsome somenum;
   output out=fulld sum=;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where agency=1;
   var siball sibsome somenum;
   output out=fulle sum=;
run;

proc sort data=fulld; by fed fam_count; where _type_ in (0,2,4,6); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data fullf;
   length cntyname $27 newgvr $6;
   set fulle fulld(in=b);
   if b and sup_cty =. then sup_cty =0;
   if b and fam_count=. then fam_count=0;
   if not b and fam_count=. then fam_count=-1;
   pctall=siball/_freq_  *100 ;  pctsome=sibsome/_freq_ *100 ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_ * 100;
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=fullf; by sup_cty fed fam_count; run;

*******************************************************************************************;
*****  Create a filler table that has one record per county for the summary and       *****;
*****  one record per county per family size for the other reports.                   *****;
*******************************************************************************************;
data fullfb;
   set fullf;
   if sup_cty=. then sup_cty=0;
run;

proc sort data=fullfb; by sup_cty fed fam_count; run;

data template;
   do sup_cty=0 to 59;
      fed=.;    do fam_count=-1 to 6; output; end;
      fed=1;    do fam_count=-1 to 6; output; end;
      fed=2;    do fam_count=-1 to 6; output; end;
      fed=3;    do fam_count=-1 to 6; output; end;
      fed=4;    do fam_count=-1 to 6; output; end;
      fed=9;    do fam_count=-1 to 6; output; end;
      fed=9.2;  do fam_count=-1 to 6; output; end;
      fed=10;   do fam_count=-1 to 6; output; end;
      fed=11;   do fam_count=-1 to 6; output; end;
      fed=13;   do fam_count=-1 to 6; output; end;
 /*   fed=14;   do fam_count=-1 to 6; output; end;  */
      fed=15;   do fam_count=-1 to 6; output; end;
      fed=16;   do fam_count=-1 to 6; output; end;
      fed=17;   do fam_count=-1 to 6; output; end;
      fed=18;   do fam_count=-1 to 6; output; end;
      fed=19;   do fam_count=-1 to 6; output; end;
      fed=20;   do fam_count=-1 to 6; output; end;
      fed=29;   do fam_count=-1 to 6; output; end;
      fed=99;   do fam_count=-1 to 6; output; end;
   end;
run;

data fullg;
   length fedname $20;
   merge fullfb template;
   by sup_cty fed fam_count;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if first.fed then do;
      fedname=put(fed,afcars.);
      siball=.; pctall=.; sibsome=.; pctsome=.;  totplc=.; totpct=.;
   end;
   else do;
      fedname=' ';
	  if _freq_=. then _freq_=0;
	  if siball=. then siball=0;
	  if pctall=. then pctall=0.0;
	  if sibsome=. then sibsome=0;
	  if pctsome=. then pctsome=0.0;
	  if totplc=. then totplc=0;
	  if totpct=. then totpct=0.0;
   end;
run;

*******************************************************************************************;
*****  Summary tables                                                                 *****;
*******************************************************************************************;

%macro summary;

data sum_&outfile.(drop=fedname cntyname newgvr FED fam_count pqtr yr); 
   set fullg;
   where fam_count=0 and fed=.; 
   if cntyname=:'State' then cntyname='California';

pqtr = qtr("01&month.&year."d);
yr = year("01&month.&year."d);

PERIOD_DT = yyq(yr,pqtr);

rename
_FREQ_ = COUNT
sup_cty = COUNTY;

AGE_GRP = 2;
P_TYPE = 1;

run;

proc append base=local.siblings_re2 data = sum_&outfile.;

%mend summary;
%summary;
%mend sibs;

%include time_period;

/*
%sibs(jan, January, 2025, jan2025);

%sibs(oct, October, 2024, oct2024);
%sibs(jul, July,    2024, jul2024);
%sibs(apr, April,   2024, apr2024);
%sibs(jan, January, 2024, jan2024);

%sibs(oct, October, 2023, oct2023);
%sibs(jul, July,    2023, jul2023);
%sibs(apr, April,   2023, apr2023);
%sibs(jan, January, 2023, jan2023);

%sibs(oct, October, 2022, oct2022);
%sibs(jul, July,    2022, jul2022);
%sibs(apr, April,   2022, apr2022);
%sibs(jan, January, 2022, jan2022);

%sibs(oct, October, 2021, oct2021);
%sibs(jul, July,    2021, jul2021);
%sibs(apr, April,   2021, apr2021);
%sibs(jan, January, 2021, jan2021);

%sibs(oct, October, 2020, oct2020);
%sibs(jul, July,    2020, jul2020);
%sibs(apr, April,   2020, apr2020);
%sibs(jan, January, 2020, jan2020);

%sibs(oct, October, 2019, oct2019);
%sibs(jul, July,    2019, jul2019);
%sibs(apr, April,   2019, apr2019);
%sibs(jan, January, 2019, jan2019);

%sibs(oct, October, 2018, oct2018);
%sibs(jul, July,    2018, jul2018);
%sibs(apr, April,   2018, apr2018);
%sibs(jan, January, 2018, jan2018);

%sibs(oct, October, 2017, oct2017);
%sibs(jul, July,    2017, jul2017);
%sibs(apr, April,   2017, apr2017);
%sibs(jan, January, 2017, jan2017);

%sibs(oct, October, 2016, oct2016);
%sibs(jul, July,    2016, jul2016);
%sibs(apr, April,   2016, apr2016);
%sibs(jan, January, 2016, jan2016);

%sibs(oct, October, 2015, oct2015);
%sibs(jul, July,    2015, jul2015);
%sibs(apr, April,   2015, apr2015);
%sibs(jan, January, 2015, jan2015);

%sibs(oct, October, 2014, oct2014);
%sibs(jul, July,    2014, jul2014);
%sibs(apr, April,   2014, apr2014);
%sibs(jan, January, 2014, jan2014);

%sibs(oct, October, 2013, oct2013);
%sibs(jul, July,    2013, jul2013);
%sibs(apr, April,   2013, apr2013);
%sibs(jan, January, 2013, jan2013);

%sibs(oct, October, 2012, oct2012);
%sibs(jul, July,    2012, jul2012);
%sibs(apr, April,   2012, apr2012);
%sibs(jan, January, 2012, jan2012);

%sibs(oct, October, 2011, oct2011);
%sibs(jul, July,    2011, jul2011);
%sibs(apr, April,   2011, apr2011);
%sibs(jan, January, 2011, jan2011);

%sibs(oct, October, 2010, oct2010);
%sibs(jul, July,    2010, jul2010);
%sibs(apr, April,   2010, apr2010);
%sibs(jan, January, 2010, jan2010);

%sibs(oct, October, 2009, oct2009);
%sibs(jul, July,    2009, jul2009);
%sibs(apr, April,   2009, apr2009);
%sibs(jan, January, 2009, jan2009);

%sibs(oct, October, 2008, oct2008);
%sibs(jul, July,    2008, jul2008);
%sibs(apr, April,   2008, apr2008);
%sibs(jan, January, 2008, jan2008);
*/
***************************************************************************;
**                                                                       **;
** SIBLING_PIT_FILES.sas                                                 **;
**                                                                       **;
** Copyright (c) 2006 Center for Social Services Research,               **;
** University of California at Berkeley. All rights reserved.            **;
** Center for Social Services Research - Univ of California at Berkeley  **;
** CWS/CMS Reporting:  Sibling Analysis                                  **;
**                                                                       **;
** This program takes a PIT file - created using a macro embedded in the **;
** program and the Sibling ID file and combines them to specify whether  **;
** siblings are placed together or not.                                  **;
**                                                                       **;
** FOR:  Barbara Needell          BY: Terry Shaw                         **;
**                                                                       **;
** This program was updated on 12/04/2002 to include the new PIT code    **;
**    (changes in ILSP and Limbo kids) and to incorporate the Placement  **;
**    /Ethnicity/and Matrix reports into a single program.               **;
** This program was updated on 9/22/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 10/9/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 9/22/2004 to enclose the program in macro **;
**    code to facilitate running multiple time periods.                  **;
** This program was updated on 3/14/2005 to change the placement home    **;
**    matching process to use IDENTIFIER rather than the created place-  **;
**    home address indicator because of confidentiality.                 **;
** This program was updated on 1/30/2005 to change the macro calling the **;
**    point in time code to use the most recent version of that code.    **;
** This program was updated on 9/26/2006 to update the way that siblings **;
**    are categorized.  Siblings are now calculated at the county level  **;
**    rather than the state level because counties do not 'see' siblings **;
**    outside of their county boundaries.                                **;
**                                                                       **;
** The following files are created:                                      **;
**     SIBf_00_xxx200y_s.xml   (Summary file)                            **;
**     SIBf_P0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_E0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_00_xxx200y_0.xml                                             **;
**                                                                       **;
**  2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group   **;
***************************************************************************;

option nocenter missing=' ';

filename time_period "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/PRODUCTS/SIBLINGS/time_period.sas";
libname userstor "/opt/sas8.2/ud_templates" ;
ods path userstor.templat(read) sashelp.tmplmst(read) ;

proc format;
   picture ntemp (round) low-<0=' '     0-<5='000,009' 5-high='000,009';
   picture dtemp (round) low-<0=' '     0.0-high='009.9';
   value vsibs  .=' '           1='1'          2='2'
                3='3'           4='4'          5='5'
                6='6+'         -1='All Children'          0='2+ Children Total*';
   value veth  .='Total'           1='Black'            2='White'
               3='Latino'        4='Asian/PI'         5='Nat. Amer.'
			   6='Missing'         0='Total';
   value afcars 1='Pre-Adopt'     
                2='Relative/NREFM'               
                3='Foster'
                4='FFA'           
                9='Court Specified Home'   
               10='Group'
               11='Shelter'        
               13='Non-FC'            
         /*    14='Guardian'  */
               15='Guardian - Dependent'    
               16='Guardian - Non-Dependent'
               17='Runaway'      
               18='Trial Home Visit' 
               19='SILP'
               20='Transitional Housing'
               29='Other'
               99='Missing'       
                .='Total' ;
   value vcnty
      00='California'           01='Alameda'           02='Alpine'
      03='Amador'               04='Butte'             05='Calaveras'
      06='Colusa'               07='Contra Costa'      08='Del Norte'
      09='El Dorado'            10='Fresno'            11='Glenn'
      12='Humboldt'             13='Imperial'          14='Inyo'
      15='Kern'                 16='Kings'             17='Lake'
      18='Lassen'               19='Los Angeles'       20='Madera'
      21='Marin'                22='Mariposa'          23='Mendocino'
      24='Merced'               25='Modoc'             26='Mono'
      27='Monterey'             28='Napa'              29='Nevada'
      30='Orange'               31='Placer'            32='Plumas'
      33='Riverside'            34='Sacramento'        35='San Benito'
      36='San Bernardino'       37='San Diego'         38='San Francisco'
      39='San Joaquin'          40='San Luis Obispo'   41='San Mateo'
      42='Santa Barbara'        43='Santa Clara'       44='Santa Cruz'
      45='Shasta'               46='Sierra'            47='Siskiyou'
      48='Solano'               49='Sonoma'            50='Stanislaus'
      51='Sutter'               52='Tehama'            53='Trinity'
      54='Tulare'               55='Tuolumne'          56='Ventura'
      57='Yolo'                 58='Yuba'              59='Missing'
	  60='Missing'
      99='Missing';
run;


*******************************************************************************************;
************************************* Begin program ***************************************;
*******************************************************************************************;

*** Set the Assignment end date into the future for ease of sorting ***;
/*
data assign;
   set cws.ASGNM_T;

   if END_DT = . then END_DT = '31DEC3000'd;

run;

*** Join the CASE and ASSIGN tables together ***;

proc sql;

   create table CASE_ASSIGN as
   select x.FKCHLD_CLT, 
          x.IDENTIFIER as caseid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.CASE_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, END_DT, END_TM;

quit;

*** Join the REFERRAL and ASSIGN tables together ***;

proc sql;

   create table REFE_ASSIGN as
   select x.IDENTIFIER as refid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.REFERL_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'R'
   order by refid, START_DT, END_DT, END_TM;

quit;
*/


/*proc sort data=cws.plc_hm_t out=plchm; by identifier; run;
data plchm; set plchm; plchmvar=1; run;

proc sort data=elf.ucb_fc         out=seons; by fkclient_t pe_s_dt oh_s_dt; run;*/
proc sort data=temp.siblings_&qt out=sibs(rename=(fkclient=fkclient_t)); by fkclient; run;

*******************************************************************************************;
************************************* Begin Macro   ***************************************;
*******************************************************************************************;

%macro sibs(month, outmonth, year, outfile); 

*******************************************************************************************;
***** Include         PIT program here - to adjust the PIT numbers - added 12/04/2002 *****;
***** * Changed to       PIT method on 9/22/2003.  This new method calculates PIT     *****;
*****   based on the month and year listed at beginning of program.                   *****;
***** * Changed to updated PIT on 5/16/07 - include actual code in program also.      *****;
*******************************************************************************************;

/*change*/
data seons_pit (rename= (pit_plc=fed));
set dwh.ucb_pit;
where agency = 1 and age le 17 and period_dt = "01&month.&year."d  /* '01JAN2019'D */ ;

*if 1800 < county < 2000 then county = 1900;
run;

*******************************************************************************************;
***** Adjust the family count - it should be based on PIT file only and it is now     *****;
***** based on the entire ELF file.                                                   *****;
*******************************************************************************************;

proc sort data=seons_pit; by fkclient_t; run;

data fredrick wilma pebbles;
   merge sibs(in=a)
         seons_pit(in=b);
   by fkclient_t;
   if fam_count ge 6 then fam_count=6;
   if a and b then output fredrick;
   else if a and not b then output wilma;
   else if b and not a then output pebbles;
run;

data fredrick2;
   set fredrick;
   sup_cty=cnty_spfcd/1;
   county = input(cnty_spfcd,best2.);
   /*change*/*zipno=zip_no;
   *drop zip_no;
run;

*** Remove ICPC children ***;

data fredrick3;
   length tfamid $12.;
   set fredrick2;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||famid);
run;

proc sort data=fredrick3 nodupkey out=newfam(rename=(fkclient_t=fkclient)); by tfamid fkclient_t; run;

data newfam2(keep=tfamid count);
   set newfam;
   retain count;
   by tfamid;
   if first.tfamid then count=0;
   count=count+1;
   if last.tfamid then do;
      if count gt 6 then count=6;
      output;
   end;
run;

proc sort data=fredrick3; by tfamid;
proc sort data=newfam2; by tfamid;

data seons_pit2;
   merge fredrick3(rename=(fam_count=oldfamcount2)) newfam2;
   by tfamid;
run;

proc sort data=seons_pit2(rename=(count=fam_count));
by fkclient_t /* pe_s_dt oh_s_dt */;
run;

*******************************************************************************************;
***** Now that the correct sibling groups are included we can continue.               *****;
*******************************************************************************************;

/*change revised to use out of home placement table - only facility ID needed*/
/*proc sql;
   create table sibplchm as
   select a.*, b.*
   from seons_pit2 as a left join plchm as b
   on a.fkplc_hm_t=b.identifier ;
quit;*/
proc sql;
        create table sibplchm as
        select a.*, b.fkplc_hm_t
        from seons_pit2 as a left join cws.o_hm_plt as b
        on a.ohmpl_id = b.identifier;
        quit;

***** The file sibplchm contains all of the Siblings info and the PLCHM stuff *****;
***** The file sibplchm has 87,995 observations - one to one match.           *****;

/*changed*/proc sort data=sibplchm; by famid /*identifier oh_s_dt*/ fkplc_hm_t fkclient_t; run;

***** Sort the file by sibling id (famid), placement home (identifi), start   *****;
***** date (oh_s_dt) and child (fkclient).  I make several temporary variables*****;
***** to find if all of the children are placed together or not.              *****;

data multisib onlykids;
   length undupvar $50;
   set sibplchm;
   if fam_count lt 2 then output onlykids;
   else do;
      *undupvar=compress(compress(street_no)||compress(street_nm)||compress(zip_no)||compress(FACLTY_NM));
      /*change*/ *undupvar=identifier; undupvar=fkplc_hm_t;
         *** USe placement home ID instead of the address and name - no longer have access ;
      output multisib;
   end;
run;

***** The data step TRYIT creates counters based on the identifiers          *****;

proc sort data=multisib; by tfamid undupvar fkclient_t; run;

data tryit;
   length tempfk $10 counter 3.;
   set multisib;
   retain tempfk counter plccount /* tempdt */;
   by tfamid undupvar fkclient_t;
   if first.tfamid then famcount=1;                              ***** Family counter for comparison            *****;
   if first.undupvar then do;
      counter=0; plccount=0; tempfk=' ' ; /* tempdt=oh_s_dt; */  ***** clear retained variables                 *****;
      if not last.undupvar then do;                              ***** if only one child dont do anything        *****;
         tempfk=fkclient_t;
         counter=1; plccount=1;tmp1=1;
      end;
   end;
   else do;
      if fkclient_t ne tempfk then do;            ***** only increase counter for diff. kids     *****;
         if fkclient_t ne lag(fkclient_t) then
            counter=counter+1;
      end;
   end;
   if last.undupvar then do;                    ***** On last record make variables as approp. *****;
      if fam_count le counter then siball=1;    ***** If counter=fam_count then all            *****;
      else if counter gt 1 then do;             ***** If counter ge 2 (more than one kid,      *****;
         sibsome=1; somenum=counter;            *****    but not all of the kids) then some    *****;
      end;
   end;
run;

***** Interested in all children in a family to have the same all/some listing *****;

proc sort data=tryit; by tfamid undupvar descending counter;

data finsib;
   retain temppa tempps tempsn ;
   set tryit;
   by tfamid undupvar ;
   if first.undupvar then do; temppa=siball; tempps=sibsome; tempsn=somenum; end;
   else do; siball=temppa; sibsome=tempps; somenum=tempsn; end;
   drop temppa tempps tempsn ;
run;

proc sort data=finsib; by tfamid descending counter;

data finsib;
   retain sibfams;
   set finsib;
   by tfamid ;
   if first.tfamid then sibfams=.;
   if first.tfamid and siball=1 then sibfams=1;
   else if first.tfamid and sibsome=1 then sibfams=1;
run;

***** Instead of unduplicating - roll any duplicate placements up *****;

proc sort data=finsib; by fkclient_T; run;

data finsib2;
   length identifi2 $10;
   retain identifi2 /* pe_s_dt2 pe_e_dt2 oh_s_dt2 oh_e_dt2 */;
   set finsib;
   by fkclient_t;
   if first.fkclient_T and last.fkclient_t then do;
   /* pe_s_dt2=.; pe_e_dt2=.; oh_s_dt2=.; oh_e_dt2=.; */
      identifi2=' '; output;
   end;
   else if first.fkclient_t and not last.fkclient_t then do; /*change*/identifi2=fkplc_hm_t;
      /*pe_s_dt2=pe_s_dt; pe_e_dt2=pe_e_dt; oh_s_dt2=oh_s_dt; oh_e_dt2=oh_e_dt;*/
   end;
   else if last.fkclient_t and not first.fkclient_t then output;
run;

***** Examine the number of sibling all/some categories compared to total *****;
***** but by the number of siblings in the system.                        *****;

proc summary data=finsib2;
   class fam_count;
   var siball sibsome sibfams somenum;
   output out=summres3 sum=;
run;

data summres3;
   set summres3;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** Put the sibfams variable on the only kids who have siblings with some placed together *****;

proc sort data=onlykids nodupkey; by fkclient_t; run;

***** Bring the files together *****;

data finale;
   set finsib2(in=a)
       onlykids(in=b);

/* Cut Missing Placement */

   where FED in (99);

   if siball ne 1 then siball=0;
   if sibsome ne 1 then sibsome=0;
   totplc=sum(siball,sibsome);
   if sup_cty lt 1 or sup_cty gt 58 then sup_cty=59;
run;

proc summary data=finale;
   class fam_count;
   var siball sibsome somenum;
   output out=summres4 sum=;
run;

data summres4;
   set summres4;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** List the number of Siblings by County *****;
***** First use valid county codes and other*****;

proc summary data=finale;
   class sup_cty fam_count;
   var siball sibsome somenum;
   output out=summres5 sum=;
run;

***** There will be two total lines - one for all kids and one for multi-siblings *****;

proc summary data=finale;
   class sup_cty fam_count;
   where fam_count gt 1;
   var siball sibsome somenum;
   output out=summres5c sum=;
run;

proc sort data=summres5c; by fam_count; where _type_ in (0,2); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data summres5b;
   length cntyname $27 newgvr $6;
   set summres5 summres5c(in=b);
   if b and sup_cty=. then sup_cty=0;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=summres5b; by newgvr fam_count; run;

***** The dataset siblingvars only has the matching variable and the flags. *****;

data pebbles;
   length tfamid $12.;
   set pebbles;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||fkclient_t);

run;

proc sort data=pebbles out=noelf; by fkclient_t; run;
proc sort data=noelf; by tfamid;
proc sort data=finale out=tf(keep=tfamid sibfams sup_cty); by tfamid; where sibfams=1; run;
proc sort data=tf nodupkey; by tfamid; run;

data noelf2;
   merge noelf(in=a) tf;
   by tfamid;
   if a;
run;

proc freq data=noelf2; table sup_cty /list missing; run;
data siblingvars;
   set finale noelf2;
   if siball=. then siball=0;
   if totplc=. then totplc=0;
   if sibfams=. then sibfams=0;
run;

*******************************************************************************************;
***** Totals by county                                                                *****;
*******************************************************************************************;

**************************************************************************************;
*****                                                                            *****;
***** Group the facility types into categories based on the following list:      *****;
*****       1='Pre-Adopt'                                                        *****;              
*****       2='Kin'                                                              *****;
*****       3='Foster'                                                           *****;
*****   3.1 4='FFA'                                                              *****;           
***** 3.5 5 9='Court Specified Home'                                             *****;
*****     9.2='Tribally Specified Home'                                          *****;
*****  4 6 10='Group'                                                            *****;
*****  5 7 11='Shelter'                                                          *****;          
***** 6 11 15='Guardian'                                                         *****; 
*****   12 16='Guardian-Other'                                                   *****;
*****      13='All Others' - including: 5.1 8 13=Non-FC, 6.1 99=Missing,         *****;
*****       7 13 17=Runaway, 8 14 18=Trial Home Visit, 9 15 19=SILP              *****;
*****      20=Transitional Housing 29=Other(?),                                  *****;
*****       10=ILSP,  12=Other-other                                             *****;
**************************************************************************************;

data sibkin;
   set siblingvars;
   if fed in (2,3,4,9,9.2,10,11,15,16) then tempfed=fed; 
   else tempfed=30;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where fam_count gt 1 and agency=1;
   var siball sibsome somenum;
   output out=fulld sum=;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where agency=1;
   var siball sibsome somenum;
   output out=fulle sum=;
run;

proc sort data=fulld; by fed fam_count; where _type_ in (0,2,4,6); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data fullf;
   length cntyname $27 newgvr $6;
   set fulle fulld(in=b);
   if b and sup_cty =. then sup_cty =0;
   if b and fam_count=. then fam_count=0;
   if not b and fam_count=. then fam_count=-1;
   pctall=siball/_freq_  *100 ;  pctsome=sibsome/_freq_ *100 ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_ * 100;
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=fullf; by sup_cty fed fam_count; run;

*******************************************************************************************;
*****  Create a filler table that has one record per county for the summary and       *****;
*****  one record per county per family size for the other reports.                   *****;
*******************************************************************************************;
data fullfb;
   set fullf;
   if sup_cty=. then sup_cty=0;
run;

proc sort data=fullfb; by sup_cty fed fam_count; run;

data template;
   do sup_cty=0 to 59;
      fed=.;    do fam_count=-1 to 6; output; end;
      fed=1;    do fam_count=-1 to 6; output; end;
      fed=2;    do fam_count=-1 to 6; output; end;
      fed=3;    do fam_count=-1 to 6; output; end;
      fed=4;    do fam_count=-1 to 6; output; end;
      fed=9;    do fam_count=-1 to 6; output; end;
      fed=9.2;  do fam_count=-1 to 6; output; end;
      fed=10;   do fam_count=-1 to 6; output; end;
      fed=11;   do fam_count=-1 to 6; output; end;
      fed=13;   do fam_count=-1 to 6; output; end;
 /*   fed=14;   do fam_count=-1 to 6; output; end;  */
      fed=15;   do fam_count=-1 to 6; output; end;
      fed=16;   do fam_count=-1 to 6; output; end;
      fed=17;   do fam_count=-1 to 6; output; end;
      fed=18;   do fam_count=-1 to 6; output; end;
      fed=19;   do fam_count=-1 to 6; output; end;
      fed=20;   do fam_count=-1 to 6; output; end;
      fed=29;   do fam_count=-1 to 6; output; end;
      fed=99;   do fam_count=-1 to 6; output; end;
   end;
run;

data fullg;
   length fedname $20;
   merge fullfb template;
   by sup_cty fed fam_count;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if first.fed then do;
      fedname=put(fed,afcars.);
      siball=.; pctall=.; sibsome=.; pctsome=.;  totplc=.; totpct=.;
   end;
   else do;
      fedname=' ';
	  if _freq_=. then _freq_=0;
	  if siball=. then siball=0;
	  if pctall=. then pctall=0.0;
	  if sibsome=. then sibsome=0;
	  if pctsome=. then pctsome=0.0;
	  if totplc=. then totplc=0;
	  if totpct=. then totpct=0.0;
   end;
run;

*******************************************************************************************;
*****  Summary tables                                                                 *****;
*******************************************************************************************;

%macro summary;

data sum_&outfile.(drop=fedname cntyname newgvr FED fam_count pqtr yr); 
   set fullg;
   where fam_count=0 and fed=.; 
   if cntyname=:'State' then cntyname='California';

pqtr = qtr("01&month.&year."d);
yr = year("01&month.&year."d);

PERIOD_DT = yyq(yr,pqtr);

rename
_FREQ_ = COUNT
sup_cty = COUNTY;

AGE_GRP = 1;
P_TYPE = 4;

run;

proc append base=local.siblings_mi1 data = sum_&outfile.;

%mend summary;
%summary;
%mend sibs;

%include time_period;

/*
%sibs(jan, January, 2025, jan2025);

%sibs(oct, October, 2024, oct2024);
%sibs(jul, July,    2024, jul2024);
%sibs(apr, April,   2024, apr2024);
%sibs(jan, January, 2024, jan2024);

%sibs(oct, October, 2023, oct2023);
%sibs(jul, July,    2023, jul2023);
%sibs(apr, April,   2023, apr2023);
%sibs(jan, January, 2023, jan2023);

%sibs(oct, October, 2022, oct2022);
%sibs(jul, July,    2022, jul2022);
%sibs(apr, April,   2022, apr2022);
%sibs(jan, January, 2022, jan2022);

%sibs(oct, October, 2021, oct2021);
%sibs(jul, July,    2021, jul2021);
%sibs(apr, April,   2021, apr2021);
%sibs(jan, January, 2021, jan2021);

%sibs(oct, October, 2020, oct2020);
%sibs(jul, July,    2020, jul2020);
%sibs(apr, April,   2020, apr2020);
%sibs(jan, January, 2020, jan2020);

%sibs(oct, October, 2019, oct2019);
%sibs(jul, July,    2019, jul2019);
%sibs(apr, April,   2019, apr2019);
%sibs(jan, January, 2019, jan2019);

%sibs(oct, October, 2018, oct2018);
%sibs(jul, July,    2018, jul2018);
%sibs(apr, April,   2018, apr2018);
%sibs(jan, January, 2018, jan2018);

%sibs(oct, October, 2017, oct2017);
%sibs(jul, July,    2017, jul2017);
%sibs(apr, April,   2017, apr2017);
%sibs(jan, January, 2017, jan2017);

%sibs(oct, October, 2016, oct2016);
%sibs(jul, July,    2016, jul2016);
%sibs(apr, April,   2016, apr2016);
%sibs(jan, January, 2016, jan2016);

%sibs(oct, October, 2015, oct2015);
%sibs(jul, July,    2015, jul2015);
%sibs(apr, April,   2015, apr2015);
%sibs(jan, January, 2015, jan2015);

%sibs(oct, October, 2014, oct2014);
%sibs(jul, July,    2014, jul2014);
%sibs(apr, April,   2014, apr2014);
%sibs(jan, January, 2014, jan2014);

%sibs(oct, October, 2013, oct2013);
%sibs(jul, July,    2013, jul2013);
%sibs(apr, April,   2013, apr2013);
%sibs(jan, January, 2013, jan2013);

%sibs(oct, October, 2012, oct2012);
%sibs(jul, July,    2012, jul2012);
%sibs(apr, April,   2012, apr2012);
%sibs(jan, January, 2012, jan2012);

%sibs(oct, October, 2011, oct2011);
%sibs(jul, July,    2011, jul2011);
%sibs(apr, April,   2011, apr2011);
%sibs(jan, January, 2011, jan2011);

%sibs(oct, October, 2010, oct2010);
%sibs(jul, July,    2010, jul2010);
%sibs(apr, April,   2010, apr2010);
%sibs(jan, January, 2010, jan2010);

%sibs(oct, October, 2009, oct2009);
%sibs(jul, July,    2009, jul2009);
%sibs(apr, April,   2009, apr2009);
%sibs(jan, January, 2009, jan2009);

%sibs(oct, October, 2008, oct2008);
%sibs(jul, July,    2008, jul2008);
%sibs(apr, April,   2008, apr2008);
%sibs(jan, January, 2008, jan2008);
*/
***************************************************************************;
**                                                                       **;
** SIBLING_PIT_FILES.sas                                                 **;
**                                                                       **;
** Copyright (c) 2006 Center for Social Services Research,               **;
** University of California at Berkeley. All rights reserved.            **;
** Center for Social Services Research - Univ of California at Berkeley  **;
** CWS/CMS Reporting:  Sibling Analysis                                  **;
**                                                                       **;
** This program takes a PIT file - created using a macro embedded in the **;
** program and the Sibling ID file and combines them to specify whether  **;
** siblings are placed together or not.                                  **;
**                                                                       **;
** FOR:  Barbara Needell          BY: Terry Shaw                         **;
**                                                                       **;
** This program was updated on 12/04/2002 to include the new PIT code    **;
**    (changes in ILSP and Limbo kids) and to incorporate the Placement  **;
**    /Ethnicity/and Matrix reports into a single program.               **;
** This program was updated on 9/22/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 10/9/2003 to include the updated PIT code **;
**    the new sibling file and the new variable naming conventions.      **;
** This program was updated on 9/22/2004 to enclose the program in macro **;
**    code to facilitate running multiple time periods.                  **;
** This program was updated on 3/14/2005 to change the placement home    **;
**    matching process to use IDENTIFIER rather than the created place-  **;
**    home address indicator because of confidentiality.                 **;
** This program was updated on 1/30/2005 to change the macro calling the **;
**    point in time code to use the most recent version of that code.    **;
** This program was updated on 9/26/2006 to update the way that siblings **;
**    are categorized.  Siblings are now calculated at the county level  **;
**    rather than the state level because counties do not 'see' siblings **;
**    outside of their county boundaries.                                **;
**                                                                       **;
** The following files are created:                                      **;
**     SIBf_00_xxx200y_s.xml   (Summary file)                            **;
**     SIBf_P0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_E0_xxx200y_xx.xml  (xx is between 0 and 58)                  **;
**     SIBf_00_xxx200y_0.xml                                             **;
**                                                                       **;
**  2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group   **;
***************************************************************************;

option nocenter missing=' ';

filename time_period "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/PRODUCTS/SIBLINGS/time_period.sas";
libname userstor "/opt/sas8.2/ud_templates" ;
ods path userstor.templat(read) sashelp.tmplmst(read) ;

proc format;
   picture ntemp (round) low-<0=' '     0-<5='000,009' 5-high='000,009';
   picture dtemp (round) low-<0=' '     0.0-high='009.9';
   value vsibs  .=' '           1='1'          2='2'
                3='3'           4='4'          5='5'
                6='6+'         -1='All Children'          0='2+ Children Total*';
   value veth  .='Total'           1='Black'            2='White'
               3='Latino'        4='Asian/PI'         5='Nat. Amer.'
			   6='Missing'         0='Total';
   value afcars 1='Pre-Adopt'     
                2='Relative/NREFM'               
                3='Foster'
                4='FFA'           
                9='Court Specified Home'   
              9.2='Tribally Specified Home'
               10='Group'
               11='Shelter'        
               13='Non-FC'            
         /*    14='Guardian'  */
               15='Guardian - Dependent'    
               16='Guardian - Non-Dependent'
               17='Runaway'      
               18='Trial Home Visit' 
               19='SILP'
               20='Transitional Housing'
               29='Other'
               99='Missing'       
                .='Total' ;
   value vcnty
      00='California'           01='Alameda'           02='Alpine'
      03='Amador'               04='Butte'             05='Calaveras'
      06='Colusa'               07='Contra Costa'      08='Del Norte'
      09='El Dorado'            10='Fresno'            11='Glenn'
      12='Humboldt'             13='Imperial'          14='Inyo'
      15='Kern'                 16='Kings'             17='Lake'
      18='Lassen'               19='Los Angeles'       20='Madera'
      21='Marin'                22='Mariposa'          23='Mendocino'
      24='Merced'               25='Modoc'             26='Mono'
      27='Monterey'             28='Napa'              29='Nevada'
      30='Orange'               31='Placer'            32='Plumas'
      33='Riverside'            34='Sacramento'        35='San Benito'
      36='San Bernardino'       37='San Diego'         38='San Francisco'
      39='San Joaquin'          40='San Luis Obispo'   41='San Mateo'
      42='Santa Barbara'        43='Santa Clara'       44='Santa Cruz'
      45='Shasta'               46='Sierra'            47='Siskiyou'
      48='Solano'               49='Sonoma'            50='Stanislaus'
      51='Sutter'               52='Tehama'            53='Trinity'
      54='Tulare'               55='Tuolumne'          56='Ventura'
      57='Yolo'                 58='Yuba'              59='Missing'
	  60='Missing'
      99='Missing';
run;


*******************************************************************************************;
************************************* Begin program ***************************************;
*******************************************************************************************;

*** Set the Assignment end date into the future for ease of sorting ***;
/*
data assign;
   set cws.ASGNM_T;

   if END_DT = . then END_DT = '31DEC3000'd;

run;

*** Join the CASE and ASSIGN tables together ***;

proc sql;

   create table CASE_ASSIGN as
   select x.FKCHLD_CLT, 
          x.IDENTIFIER as caseid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.CASE_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, END_DT, END_TM;

quit;

*** Join the REFERRAL and ASSIGN tables together ***;

proc sql;

   create table REFE_ASSIGN as
   select x.IDENTIFIER as refid,
          y.START_DT, 
          y.END_DT, 
          y.END_TM,
          y.CNTY_SPFCD
   from cws.REFERL_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'R'
   order by refid, START_DT, END_DT, END_TM;

quit;
*/

/*proc sort data=cws.plc_hm_t out=plchm; by identifier; run;
data plchm; set plchm; plchmvar=1; run;

proc sort data=elf.ucb_fc         out=seons; by fkclient_t pe_s_dt oh_s_dt; run;*/
proc sort data=temp.siblings_&qt out=sibs(rename=(fkclient=fkclient_t)); by fkclient; run;

*******************************************************************************************;
************************************* Begin Macro   ***************************************;
*******************************************************************************************;

%macro sibs(month, outmonth, year, outfile); 

*******************************************************************************************;
***** Include         PIT program here - to adjust the PIT numbers - added 12/04/2002 *****;
***** * Changed to       PIT method on 9/22/2003.  This new method calculates PIT     *****;
*****   based on the month and year listed at beginning of program.                   *****;
***** * Changed to updated PIT on 5/16/07 - include actual code in program also.      *****;
*******************************************************************************************;

/*change*/
data seons_pit (rename= (pit_plc=fed));
set dwh.ucb_pit;
where agency = 1 and age le 20 and period_dt = "01&month.&year."d  /* '01JAN2019'D */ ;

*if 1800 < county < 2000 then county = 1900;
run;


*******************************************************************************************;
***** Adjust the family count - it should be based on PIT file only and it is now     *****;
***** based on the entire ELF file.                                                   *****;
*******************************************************************************************;

proc sort data=seons_pit; by fkclient_t; run;

data fredrick wilma pebbles;
   merge sibs(in=a)
         seons_pit(in=b);
   by fkclient_t;
   if fam_count ge 6 then fam_count=6;
   if a and b then output fredrick;
   else if a and not b then output wilma;
   else if b and not a then output pebbles;
run;

data fredrick2;
   set fredrick;
   sup_cty=cnty_spfcd/1;
   county = input(cnty_spfcd,best2.);
   /*change*/*zipno=zip_no;
   *drop zip_no;
run;

*** Remove ICPC children ***;

data fredrick3;
   length tfamid $12.;
   set fredrick2;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||famid);
run;

proc sort data=fredrick3 nodupkey out=newfam(rename=(fkclient_t=fkclient)); by tfamid fkclient_t; run;

data newfam2(keep=tfamid count);
   set newfam;
   retain count;
   by tfamid;
   if first.tfamid then count=0;
   count=count+1;
   if last.tfamid then do;
      if count gt 6 then count=6;
      output;
   end;
run;

proc sort data=fredrick3; by tfamid;
proc sort data=newfam2; by tfamid;

data seons_pit2;
   merge fredrick3(rename=(fam_count=oldfamcount2)) newfam2;
   by tfamid;
run;

proc sort data=seons_pit2(rename=(count=fam_count));
by fkclient_t /* pe_s_dt oh_s_dt */;
run;

*******************************************************************************************;
***** Now that the correct sibling groups are included we can continue.               *****;
*******************************************************************************************;

/*change revised to use out of home placement table - only facility ID needed*/
/*proc sql;
   create table sibplchm as
   select a.*, b.*
   from seons_pit2 as a left join plchm as b
   on a.fkplc_hm_t=b.identifier ;
quit;*/
proc sql;
        create table sibplchm as
        select a.*, b.fkplc_hm_t
        from seons_pit2 as a left join cws.o_hm_plt as b
        on a.ohmpl_id = b.identifier;
        quit;

***** The file sibplchm contains all of the Siblings info and the PLCHM stuff *****;
***** The file sibplchm has 87,995 observations - one to one match.           *****;

/*changed*/proc sort data=sibplchm; by famid /*identifier oh_s_dt*/ fkplc_hm_t fkclient_t; run;

***** Sort the file by sibling id (famid), placement home (identifi), start   *****;
***** date (oh_s_dt) and child (fkclient).  I make several temporary variables*****;
***** to find if all of the children are placed together or not.              *****;

data multisib onlykids;
   length undupvar $50;
   set sibplchm;
   if fam_count lt 2 then output onlykids;
   else do;
      *undupvar=compress(compress(street_no)||compress(street_nm)||compress(zip_no)||compress(FACLTY_NM));
      /*change*/ *undupvar=identifier; undupvar=fkplc_hm_t;
         *** USe placement home ID instead of the address and name - no longer have access ;
      output multisib;
   end;
run;

***** The data step TRYIT creates counters based on the identifiers          *****;

proc sort data=multisib; by tfamid undupvar fkclient_t; run;

data tryit;
   length tempfk $10 counter 3.;
   set multisib;
   retain tempfk counter plccount tempdt;
   by tfamid undupvar fkclient_t;
   if first.tfamid then famcount=1;                             ***** Family counter for comparison            *****;
   if first.undupvar then do;
      counter=0; plccount=0; tempfk=' ' ; /* tempdt=oh_s_dt; */ ***** clear retained variables                 *****;
      if not last.undupvar then do;                             ***** if only one child dont do anything        *****;
         tempfk=fkclient_t;
         counter=1; plccount=1;tmp1=1;
      end;
   end;
   else do;
      if fkclient_t ne tempfk then do;            ***** only increase counter for diff. kids     *****;
         if fkclient_t ne lag(fkclient_t) then
            counter=counter+1;
      end;
   end;
   if last.undupvar then do;                    ***** On last record make variables as approp. *****;
      if fam_count le counter then siball=1;    ***** If counter=fam_count then all            *****;
      else if counter gt 1 then do;             ***** If counter ge 2 (more than one kid,      *****;
         sibsome=1; somenum=counter;            *****    but not all of the kids) then some    *****;
      end;
   end;
run;

***** Interested in all children in a family to have the same all/some listing *****;

proc sort data=tryit; by tfamid undupvar descending counter;

data finsib;
   retain temppa tempps tempsn ;
   set tryit;
   by tfamid undupvar ;
   if first.undupvar then do; temppa=siball; tempps=sibsome; tempsn=somenum; end;
   else do; siball=temppa; sibsome=tempps; somenum=tempsn; end;
   drop temppa tempps tempsn ;
run;

proc sort data=finsib; by tfamid descending counter;

data finsib;
   retain sibfams;
   set finsib;
   by tfamid ;
   if first.tfamid then sibfams=.;
   if first.tfamid and siball=1 then sibfams=1;
   else if first.tfamid and sibsome=1 then sibfams=1;
run;

***** Instead of unduplicating - roll any duplicate placements up *****;

proc sort data=finsib; by fkclient_T; run;

data finsib2;
   length identifi2 $10;
   retain identifi2 pe_s_dt2 pe_e_dt2 oh_s_dt2 oh_e_dt2;
   set finsib;
   by fkclient_t;
   if first.fkclient_T and last.fkclient_t then do;
      /*pe_s_dt2=.; pe_e_dt2=.; oh_s_dt2=.; oh_e_dt2=.;*/
      identifi2=' '; output;
   end;
   else if first.fkclient_t and not last.fkclient_t then do; /*change*/identifi2=fkplc_hm_t;
      /*pe_s_dt2=pe_s_dt; pe_e_dt2=pe_e_dt; oh_s_dt2=oh_s_dt; oh_e_dt2=oh_e_dt;*/
   end;
   else if last.fkclient_t and not first.fkclient_t then output;
run;

***** Examine the number of sibling all/some categories compared to total *****;
***** but by the number of siblings in the system.                        *****;

proc summary data=finsib2;
   class fam_count;
   var siball sibsome sibfams somenum;
   output out=summres3 sum=;
run;

data summres3;
   set summres3;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** Put the sibfams variable on the only kids who have siblings with some placed together *****;

proc sort data=onlykids nodupkey; by fkclient_t; run;

***** Bring the files together *****;

data finale;
   set finsib2(in=a)
       onlykids(in=b);

/* Cut Missing Placement */
   where FED in (99);

   if siball ne 1 then siball=0;
   if sibsome ne 1 then sibsome=0;
   totplc=sum(siball,sibsome);
   if sup_cty lt 1 or sup_cty gt 58 then sup_cty=59;
run;

proc summary data=finale;
   class fam_count;
   var siball sibsome somenum;
   output out=summres4 sum=;
run;

data summres4;
   set summres4;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
run;

***** List the number of Siblings by County *****;
***** First use valid county codes and other*****;

proc summary data=finale;
   class sup_cty fam_count;
   var siball sibsome somenum;
   output out=summres5 sum=;
run;

***** There will be two total lines - one for all kids and one for multi-siblings *****;

proc summary data=finale;
   class sup_cty fam_count;
   where fam_count gt 1;
   var siball sibsome somenum;
   output out=summres5c sum=;
run;

proc sort data=summres5c; by fam_count; where _type_ in (0,2); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data summres5b;
   length cntyname $27 newgvr $6;
   set summres5 summres5c(in=b);
   if b and sup_cty=. then sup_cty=0;
   pctall=siball/_freq_  ;  pctsome=sibsome/_freq_  ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=summres5b; by newgvr fam_count; run;

***** The dataset siblingvars only has the matching variable and the flags. *****;

data pebbles;
   length tfamid $12.;
   set pebbles;
   if county=60 then delete;
   *if not (1 le county le 59) then county=60;
   tfamid=compress(put(county,z2.)||fkclient_t);

run;

proc sort data=pebbles out=noelf; by fkclient_t; run;
proc sort data=noelf; by tfamid;
proc sort data=finale out=tf(keep=tfamid sibfams sup_cty); by tfamid; where sibfams=1; run;
proc sort data=tf nodupkey; by tfamid; run;

data noelf2;
   merge noelf(in=a) tf;
   by tfamid;
   if a;
run;

proc freq data=noelf2; table sup_cty /list missing; run;
data siblingvars;
   set finale noelf2;
   if siball=. then siball=0;
   if totplc=. then totplc=0;
   if sibfams=. then sibfams=0;
run;

*******************************************************************************************;
***** Totals by county                                                                *****;
*******************************************************************************************;

**************************************************************************************;
*****                                                                            *****;
***** Group the facility types into categories based on the following list:      *****;
*****       1='Pre-Adopt'                                                        *****;              
*****       2='Kin'                                                              *****;
*****       3='Foster'                                                           *****;
*****   3.1 4='FFA'                                                              *****;           
***** 3.5 5 9='Court Specified Home'                                             *****;
*****     9.2='Tribally Specified Home'                                          *****;
*****  4 6 10='Group'                                                            *****;
*****  5 7 11='Shelter'                                                          *****;          
***** 6 11 15='Guardian'                                                         *****; 
*****   12 16='Guardian-Other'                                                   *****;
*****      13='All Others' - including: 5.1 8 13=Non-FC, 6.1 99=Missing,         *****;
*****       7 13 17=Runaway, 8 14 18=Trial Home Visit, 9 15 19=SILP              *****;
*****      20=Transitional Housing 29=Other(?),                                  *****;
*****       10=ILSP,  12=Other-other                                             *****;
**************************************************************************************;

data sibkin;
   set siblingvars;
   if fed in (2,3,4,9,9.2,10,11,15,16) then tempfed=fed; 
   else tempfed=30;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where fam_count gt 1 and agency=1;
   var siball sibsome somenum;
   output out=fulld sum=;
run;

proc summary data=sibkin;
   class sup_cty fed fam_count;
   where agency=1;
   var siball sibsome somenum;
   output out=fulle sum=;
run;

proc sort data=fulld; by fed fam_count; where _type_ in (0,2,4,6); run;

***** Create a new sorter variable to allow the correct placement of both summary *****;
***** lines of data - the all kids and the multiple sibling groups.               *****;

data fullf;
   length cntyname $27 newgvr $6;
   set fulle fulld(in=b);
   if b and sup_cty =. then sup_cty =0;
   if b and fam_count=. then fam_count=0;
   if not b and fam_count=. then fam_count=-1;
   pctall=siball/_freq_  *100 ;  pctsome=sibsome/_freq_ *100 ;
   totplc=sum(siball,sibsome);
   totpct=totplc/_freq_ * 100;
   if b and fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'b');
      else newgvr=compress(sup_cty||'b');
      if sup_cty ne 0 then cntyname=compress(cntyname||' (multi)');
   end;
   else if fam_count=. then do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'a');
      else newgvr=compress(sup_cty||'a');
   end;
   else do;
      if sup_cty lt 10 then newgvr=compress('0'||sup_cty||'c');
      else newgvr=compress(sup_cty||'c');
   end;
run;

proc sort data=fullf; by sup_cty fed fam_count; run;

*******************************************************************************************;
*****  Create a filler table that has one record per county for the summary and       *****;
*****  one record per county per family size for the other reports.                   *****;
*******************************************************************************************;
data fullfb;
   set fullf;
   if sup_cty=. then sup_cty=0;
run;

proc sort data=fullfb; by sup_cty fed fam_count; run;

data template;
   do sup_cty=0 to 59;
      fed=.;    do fam_count=-1 to 6; output; end;
      fed=1;    do fam_count=-1 to 6; output; end;
      fed=2;    do fam_count=-1 to 6; output; end;
      fed=3;    do fam_count=-1 to 6; output; end;
      fed=4;    do fam_count=-1 to 6; output; end;
      fed=9;    do fam_count=-1 to 6; output; end;
      fed=9.2;  do fam_count=-1 to 6; output; end;
      fed=10;   do fam_count=-1 to 6; output; end;
      fed=11;   do fam_count=-1 to 6; output; end;
      fed=13;   do fam_count=-1 to 6; output; end;
 /*   fed=14;   do fam_count=-1 to 6; output; end;  */
      fed=15;   do fam_count=-1 to 6; output; end;
      fed=16;   do fam_count=-1 to 6; output; end;
      fed=17;   do fam_count=-1 to 6; output; end;
      fed=18;   do fam_count=-1 to 6; output; end;
      fed=19;   do fam_count=-1 to 6; output; end;
      fed=20;   do fam_count=-1 to 6; output; end;
      fed=29;   do fam_count=-1 to 6; output; end;
      fed=99;   do fam_count=-1 to 6; output; end;
   end;
run;

data fullg;
   length fedname $20;
   merge fullfb template;
   by sup_cty fed fam_count;
   cntyname=put(sup_cty,vcnty.);
   if sup_cty=. then do; sup_cty=0; cntyname='State of California'; end;
   else if sup_cty=0 then cntyname='State of California (multi)';
   else if sup_cty=59 then cntyname='Missing';
   if first.fed then do;
      fedname=put(fed,afcars.);
      siball=.; pctall=.; sibsome=.; pctsome=.;  totplc=.; totpct=.;
   end;
   else do;
      fedname=' ';
	  if _freq_=. then _freq_=0;
	  if siball=. then siball=0;
	  if pctall=. then pctall=0.0;
	  if sibsome=. then sibsome=0;
	  if pctsome=. then pctsome=0.0;
	  if totplc=. then totplc=0;
	  if totpct=. then totpct=0.0;
   end;
run;

*******************************************************************************************;
*****  Summary tables                                                                 *****;
*******************************************************************************************;

%macro summary;

data sum_&outfile.(drop=fedname cntyname newgvr FED fam_count pqtr yr); 
   set fullg;
   where fam_count=0 and fed=.; 
   if cntyname=:'State' then cntyname='California';

pqtr = qtr("01&month.&year."d);
yr = year("01&month.&year."d);

PERIOD_DT = yyq(yr,pqtr);

rename
_FREQ_ = COUNT
sup_cty = COUNTY;

AGE_GRP = 2;
P_TYPE = 4;

run;

proc append base=local.siblings_mi2 data = sum_&outfile.;

%mend summary;
%summary;
%mend sibs;

%include time_period;

/*
%sibs(jan, January, 2025, jan2025);

%sibs(oct, October, 2024, oct2024);
%sibs(jul, July,    2024, jul2024);
%sibs(apr, April,   2024, apr2024);
%sibs(jan, January, 2024, jan2024);

%sibs(oct, October, 2023, oct2023);
%sibs(jul, July,    2023, jul2023);
%sibs(apr, April,   2023, apr2023);
%sibs(jan, January, 2023, jan2023);

%sibs(oct, October, 2022, oct2022);
%sibs(jul, July,    2022, jul2022);
%sibs(apr, April,   2022, apr2022);
%sibs(jan, January, 2022, jan2022);

%sibs(oct, October, 2021, oct2021);
%sibs(jul, July,    2021, jul2021);
%sibs(apr, April,   2021, apr2021);
%sibs(jan, January, 2021, jan2021);

%sibs(oct, October, 2020, oct2020);
%sibs(jul, July,    2020, jul2020);
%sibs(apr, April,   2020, apr2020);
%sibs(jan, January, 2020, jan2020);

%sibs(oct, October, 2019, oct2019);
%sibs(jul, July,    2019, jul2019);
%sibs(apr, April,   2019, apr2019);
%sibs(jan, January, 2019, jan2019);

%sibs(oct, October, 2018, oct2018);
%sibs(jul, July,    2018, jul2018);
%sibs(apr, April,   2018, apr2018);
%sibs(jan, January, 2018, jan2018);

%sibs(oct, October, 2017, oct2017);
%sibs(jul, July,    2017, jul2017);
%sibs(apr, April,   2017, apr2017);
%sibs(jan, January, 2017, jan2017);

%sibs(oct, October, 2016, oct2016);
%sibs(jul, July,    2016, jul2016);
%sibs(apr, April,   2016, apr2016);
%sibs(jan, January, 2016, jan2016);

%sibs(oct, October, 2015, oct2015);
%sibs(jul, July,    2015, jul2015);
%sibs(apr, April,   2015, apr2015);
%sibs(jan, January, 2015, jan2015);

%sibs(oct, October, 2014, oct2014);
%sibs(jul, July,    2014, jul2014);
%sibs(apr, April,   2014, apr2014);
%sibs(jan, January, 2014, jan2014);

%sibs(oct, October, 2013, oct2013);
%sibs(jul, July,    2013, jul2013);
%sibs(apr, April,   2013, apr2013);
%sibs(jan, January, 2013, jan2013);

%sibs(oct, October, 2012, oct2012);
%sibs(jul, July,    2012, jul2012);
%sibs(apr, April,   2012, apr2012);
%sibs(jan, January, 2012, jan2012);

%sibs(oct, October, 2011, oct2011);
%sibs(jul, July,    2011, jul2011);
%sibs(apr, April,   2011, apr2011);
%sibs(jan, January, 2011, jan2011);

%sibs(oct, October, 2010, oct2010);
%sibs(jul, July,    2010, jul2010);
%sibs(apr, April,   2010, apr2010);
%sibs(jan, January, 2010, jan2010);

%sibs(oct, October, 2009, oct2009);
%sibs(jul, July,    2009, jul2009);
%sibs(apr, April,   2009, apr2009);
%sibs(jan, January, 2009, jan2009);

%sibs(oct, October, 2008, oct2008);
%sibs(jul, July,    2008, jul2008);
%sibs(apr, April,   2008, apr2008);
%sibs(jan, January, 2008, jan2008);
*/