**************************************************************************************; ** This program takes the PIT file from SEAN and incorporates the Sibling ID file **; ** run earlier, combine them and use them to specify a variable which will allow us **; ** to count the number of siblings placed together (either all together, or **; ** partially together). **; ** **; ** FOR: Barbara Needell BY: Terry Shaw **; ** **; ** The following files are created: **; ** grid_sXr_mmmyyyy_0.html **; ** grid_sXrxxx_mmmyyyy_0.html **; ** grid_sXrsum_mmmyyyy_0.html **; ** (where xxx is the specific category of care, KIN=kinship, FOS=foster, etc. **; ** mmm is the specific month - currently either January or July. **; ** yyyy is the specific year ). **; ** **; ** UPDATED: 1/8/2004-Changes based on QA to titles etc. **; ** UPDATED: 6/24/2004-Ages 16 through 21 Only. Summary only. **; ** UPDATED: 7/5/2004-Changed ages to 16 through 20. **; ** UPDATED: 10/22/2004-Added MACRO coding and PROC REPORT. **; ** UPDATED: 10/23/2005-Updated MACRO dates. **; ** UPDATED: 03/25/2009-Assigning PLC_FCLC = '1415' to 'Foster' **; ** @PIT_MACRO_NEW.sas **; ** UPDATED: 06/14/2018-Updated PIT_MACRO_2018.sas **; ** **; **************************************************************************************; 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 elf "/ssa4/SAS/SASDATA/CWS_CMS/&quarter./"; libname dwh "/ssa4/SAS/SASDATA/CWS_CMS/&quarter./"; libname tvs '/dss/TSHAW'; libname temp '/dss/temp'; libname userstor "!sasroot/ud_templates" ; ods path userstor.templat(read) sashelp.tmplmst(read) ; options errors=3 symbolgen missing=' ' nodate nonumber nocenter; ******* USER-SUPPLIED %LET STATEMENTS *************** ; proc format; picture ntemp low-<0=' ' 1-<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 afcars 1='Pre-Adopt' 2='Relative/NREFM' 3='Foster' 4='FFA' 9='Court Specified Home' 9.2='Tribally Approved Home' 10='Group' 11='Shelter' 13='Non-FC' 15='Guardian - Dependent' 16='Guardian - Non-Dependent' 17='Runaway' 18='Trial Home Visit' 19='SILP' 20='Transitional Housing' 29='Other' 99='Missing'; /* value newfed 1='Pre-Adopt' 2='Relative/NREFM' 3='Foster' 4='FFA' 9='Court Specified Home' 9.2='Tribally Approved Home' 10='Group' 11='Shelter' 13='Non-FC' 15='Guardian - Dependent' 16='Guardian - Non-Dependent' 17='Runaway' 18='Trial Home Visit' 19='SILP' 20='Transitional Housing' 29='Other' 99='Missing' .='All Types'; value newfed 1='Pre-Adopt' 2='Relative/NREFM' 3='Foster' 4='FFA' 5='Court Specified Home' 6='Tribally Approved Home' 7='Group' 8='Shelter' 9='Non-FC' 10='Guardian - Dependent' 11='Guardian - Non-Dependent' 12='Runaway' 13='Trial Home Visit' 14='SILP' 15='Transitional Housing' 16='Other' 17='Missing' .='All Types'; */ value newfed 1='Relative/NREFM' 2='Foster' 3='FFA' 4='Group' 5='Other'; value fedtit 1='Kin' 2='Fos' 3='FFA' 4='Grp' 5='Oth' .='All Types' ; value veth .='Total' 1='Black' 2='White' 3='Latino' 4='Asian/Other' 5='Nat. Amer.' 6='Missing'; Value ResCty 1068 = '01' 1069 = '02' 1070 = '03' 1071 = '04' 1072 = '05' 1073 = '06' 1074 = '07' 1075 = '08' 1076 = '09' 1077 = '10' 1078 = '11' 1079 = '12' 1080 = '13' 1081 = '14' 1082 = '15' 1083 = '16' 1084 = '17' 1085 = '18' 1086 = '19' 1087 = '20' 1088 = '21' 1089 = '22' 1090 = '23' 1091 = '24' 1092 = '25' 1093 = '26' 1094 = '27' 1095 = '28' 1096 = '29' 1097 = '30' 1098 = '31' 1099 = '32' 1100 = '33' 1101 = '34' 1102 = '35' 1103 = '36' 1104 = '37' 1105 = '38' 1106 = '39' 1107 = '40' 1108 = '41' 1109 = '42' 1110 = '43' 1111 = '44' 1112 = '45' 1113 = '46' 1114 = '47' 1115 = '48' 1116 = '49' 1117 = '50' 1118 = '51' 1119 = '52' 1120 = '53' 1121 = '54' 1122 = '55' 1123 = '56' 1124 = '57' 1125 = '58' 1126 = '59' 9999 = '60' 0 = '59' . = '60'; value vcnty -1='All Counties*' 00='Total in Cty' 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' 99='Missing' 59='Missing' 60='Out of State'; run; *** Use macro variable date for putting a date on the output data set. ; *******************************************************************************************; ************************************* Begin program ***************************************; *******************************************************************************************; %macro timeframe(fmonth,month,year); *******************************************************************************************; ***** Include Daniels PIT program here - to adjust the PIT numbers - added 12/04/2002 *****; *******************************************************************************************; *******************************************************************************************; ***** * Changed to Seans PIT method on 11/04/2003. This new method calculates PIT *****; ***** based on the month and year listed at beginning of program. *****; *******************************************************************************************; *** %inc '/home/tshaw/Macros/PIT_Probation_Generic.sas'; *** %inc '/home/tshaw/Macros/PIT_CHILDWELFARE_UCBFC_GENERIC.SAS'; *******************************************************************************************; ***** Adjust the family count - it should be based on PIT file only and it is now *****; ***** based on the entire ELF file. *****; *******************************************************************************************; /*everything above this point in the original program is done in the following?*/ /*note oh)cnty codes 59 to 62 unique to this process*/ data table1a (keep = pit_plc cnty_spfcd rescty numvar rename = (pit_plc = fed cnty_spfcd = supcty)); set dwh.ucb_pit; where period_dt = "01&month.&year"D and agency = 1 and (16 le age le 21); numvar = 1; if oh_cnty = '70' then rescty = '59'; /*other state*/ else if oh_cnty = '80' then rescty = '60'; /*other country*/ else if oh_cnty = '90' or pit_plc = 17 then rescty = '61'; /*runaway*/ else if oh_cnty in('98', '99') then rescty = '62'; /*missing*/ else rescty = oh_cnty; run; *** We need to invoke Proc Means twice, first to process the *** data for doing reports by Federal Facility Type, i.e., *** by ResCty SupCty fed. Since we are counting records, we cannot *** first count by ResCty SupCty fed and then invoke Proc Means *** again to count by ResCty SupCty. This would work if we were *** adding numbers, but not counting records. ; *** Next, we invoke Proc Means to process the data for doing reports by all Federal *** Facility Types, i.e., data for doing reports by by ResCty SupCty. *** The Point-in-Time Probation data set may or may not contain all Federal *** Facility Types. For example, the January 1, 2003 Point-in-Time Caseload data set *** contained only the Federal Facility Types for youth in kin, foster, FFA, *** and group home placements. ; proc means data = table1a nway noprint ; class ResCty SupCty ; var NumVar ; output out = table_AllFedTypes (keep=ResCty SupCty count) N=count; run ; proc sort data = table_AllFedTypes out = GridOwn&date._AllFedTypes ; by SupCty ResCty ; run ; proc datasets nolist ; delete table_AllFedTypes ; run ; data GridOwn&date._AllFedTypes ; set GridOwn&date._AllFedTypes ; if supcty=. then supcty=0; run; data tryit; set GridOwn&date._AllFedTypes ; by SupCty ResCty ; *retain rc1-rc60 ; retain rc1-rc62 ; *array r{60} rc1-rc60; array r{62} rc1-rc62; n_rc=rescty/1; n_sc=supcty/1; if n_sc=. then n_sc=0; if first.supcty then do; *do j=1 to 60; do j=1 to 62; r{j}=.; end; end; * 1 le n_rc le 59 then r{n_rc}=count; if 1 le n_rc le 62 then r{n_rc}=count; *else rc60=sum(rc60,count); else rc63=sum(rc63,count); if last.supcty then do; tot=sum(of rc1-rc62); totm=sum(of rc1-rc60); if 1 le n_sc le 58 then do; pct=(r{n_sc}/tot)*100; pctm=(r{n_sc}/totm)*100; tot_in_county=r{n_sc}; end; * else do; * pct=(r{n_sc}/tot)*100; * tot_in_state=0; * end; if pct=. then pct=0; output; end; keep n_sc tot totm rc1-rc62 pct pctm tot_in_county; run; /*start here - policy question regarding runaways and missing in or out of state?*/ proc summary data=tryit; var tot totm rc1-rc62 tot_in_county; output out=summary_table sum=; run; data tryit2; set summary_table(in=a) tryit; if a then do; /*only calculates for summary data*/ n_sc=0; output; n_sc=-1; CA_in=sum(sum(of rc1-rc58)); totm = (sum(of rc1-rc60)); pct=(tot_in_county/tot)*100; pctm=(tot_in_county/totm)*100; pct2=(ca_in/tot)*100; output; end; else output; run; proc sort data=tryit2; by n_sc; run; data tryit3; set tryit2; by n_sc; *array r{60} rc1-rc60; array r{63} rc1-rc63; if n_sc=-1 then do; *do i=1 to 60; do i=1 to 62; r{i}=.; end; end; if n_sc=0 then do; tot=.; pct=.; end; run; proc print data = tryit3; run; data filler_cnty; do i=1 to 58; n_sc=i; output; end; drop i; run; proc sort data=filler_cnty; by n_sc; proc sort data=tryit3; by n_sc; data tryit_filled; format pct 5.1; merge filler_cnty tryit3; by n_sc; *array r{60} rc1-rc60; array r{63} rc1-rc63; if n_sc ge 0 then do; *do i=1 to 60; do i=1 to 63; if r{i}=. then r{i}=0; end; if n_sc gt 0 then do; if tot=. then tot=0; if pct=. then pct=0.0; if pctm=. then pctm=0.0; end; end; run; title; footnote; ods html file="/pool01/TEMP/CWS_CMS/SLEE/&quarter./Output/PL_GRIDS/CWAges1621/cw_grid_sXrAge1621_&month.&year._0.html" (title="&fmonth. 1, &year. Child Welfare Supervised Foster Care, Out-of-County Placements") style=styles.pir3 newfile=PAGE; /* ods xml file="/pool01/TEMP/CWS_CMS/SLEE/&quarter./Output/PL_GRIDS/CWAges1621/cw_grid_sXrAge1621_&month.&year._0.xml" (title="&fmonth. 1, &year. Child Welfare Supervised Foster Care, Out-of-County Placements"); */ title1 height=8pt font="arial" color=black "California Child Welfare Indicators Project (CCWIP)"; title2 height=8pt font="arial" color=black "University of California at Berkeley"; title3 height=8pt font="arial" color=black "California Department of Social Services, Research and Data Insights Branch"; title4 height=12pt font="arial" "&fmonth. 1, &year. Children in Child Welfare Supervised Foster Care "; title5 height=12pt font="arial" 'Out-of-County Placements: Supervising County (row) and Placement County (column)'; footnote1 height=8pt font="arial" color=black "Data Source: CWS/CMS &dataq. Extract."; footnote2 height=8pt font="arial" color=black '* All Counties consists of the total for California and the Overall Percent Placed In County'; footnote3 height=8pt font="arial" color=black "The suggested way to cite the above data is as follows:"; footnote4 height=8pt font="arial" color=black "CCWIP Reports, from University of California at Berkeley California Child Welfare Indicators Project website. URL: "; proc print data=tryit_filled noobs split='*' heading=V; var n_sc tot pct rc1-rc62 ; options missing=' '; label rc1 ='Alameda' rc2 ='Alpine' rc59='Out of State' rc3 ='Amador' rc4 ='Butte' rc5 ='Calaveras' rc6 ='Colusa' rc7 ='Contra Costa' rc8 ='Del Norte' rc9 ='El Dorado' rc10='Fresno' rc11='Glenn' rc12='Humboldt' rc13='Imperial' rc14='Inyo' rc15='Kern' rc16='Kings' rc17='Lake' rc18='Lassen' rc19='Los Angeles' rc20='Madera' rc21='Marin' rc22='Mariposa' rc23='Mendocino' rc24='Merced' rc25='Modoc' rc26='Mono' rc27='Monterey' rc28='Napa' rc29='Nevada' rc30='Orange' rc31='Placer' rc32='Plumas' rc33='Riverside' rc34='Sacramento' rc35='San Benito' rc36='San Bernardino' rc37='San Diego' rc38='San Francisco' rc39='San Joaquin' rc40='San Luis Obispo' rc41='San Mateo' rc42='Santa Barbara' rc43='Santa Clara' rc44='Santa Cruz' rc45='Shasta' rc46='Sierra' rc47='Siskiyou' rc48='Solano' rc49='Sonoma' rc50='Stanislaus' rc51='Sutter' rc52='Tehama' rc53='Trinity' rc54='Tulare' rc55='Tuolumne' rc56='Ventura' rc57='Yolo' rc58='Yuba' /*rc60='Missing'*/ rc59='Other State' rc60='Other Country' rc61='Runaway' rc62='Missing' tot='Children' n_sc='Supervising County' pct='In County %'; format tot rc1-rc60 ntemp. n_sc vcnty. pct dtemp.; run; *ods xml close; ods html close; %mend timeframe; %timeframe(January,jan,2025); %timeframe(January,jan,2024); %timeframe(January,jan,2023); %timeframe(January,jan,2022); %timeframe(January,jan,2021); %timeframe(January,jan,2020); %timeframe(January,jan,2019); %timeframe(January,jan,2018); %timeframe(January,jan,2017); %timeframe(January,jan,2016); %timeframe(January,jan,2015); %timeframe(January,jan,2014); /* %timeframe(January,jan,2013); %timeframe(January,jan,2012); %timeframe(January,jan,2011); %timeframe(January,jan,2010); %timeframe(January,jan,2009); %timeframe(January,jan,2008); %timeframe(January,jan,2007); %timeframe(January,jan,2006); %timeframe(January,jan,2005); %timeframe(January,jan,2004); %timeframe(January,jan,2003); %timeframe(January,jan,2002); %timeframe(January,jan,2001); %timeframe(January,jan,2000); %timeframe(January,jan,1999); */ %timeframe(July,jul,2024); %timeframe(July,jul,2023); %timeframe(July,jul,2022); %timeframe(July,jul,2021); %timeframe(July,jul,2020); %timeframe(July,jul,2019); %timeframe(July,jul,2018); %timeframe(July,jul,2017); %timeframe(July,jul,2016); %timeframe(July,jul,2015); %timeframe(July,jul,2014); /* %timeframe(July,jul,2013); %timeframe(July,jul,2012); %timeframe(July,jul,2011); %timeframe(July,jul,2010); %timeframe(July,jul,2009); %timeframe(July,jul,2008); %timeframe(July,jul,2007); %timeframe(July,jul,2006); %timeframe(July,jul,2005); %timeframe(July,jul,2004); %timeframe(July,jul,2003); %timeframe(July,jul,2002); %timeframe(July,jul,2001); %timeframe(July,jul,2000); %timeframe(July,jul,1999); %timeframe(July,jul,1998); */