data test0;
set TAY.tay_edd_nsc_2023q4;

rename
PREDOM_FT_18 = PREDOM_FT
flag_08 = FLAG_8
pre18_plc_fclc = LAST_AGE18_OHP
ind_time_in = TIME_IN
age18_yr = AGE_18_YR;

run;

data test1;
set test0;

keep 
FKCLIENT_T
any_16to18
any_18to19
any_18to20
any_18to21
any_18to22
any_18to23
PREDOM_FT
GENDER_CD
AGENCY
P_ETHNCTYC
pe1_s_age
flag_8
last_age18_ohp
time_in
age19_status
age20_status
age21_status
age22_status
age23_status
CNTY_SPFCD
HISP_CD
COUNTY
age_18_yr;

run;

data test2(drop=GENDER_CD P_ETHNCTYC HISP_CD);
set test1;

if any_16to18 = 0 then any_16to18 = 2;
if any_18to19 = 0 then any_18to19 = 2;
if any_18to20 = 0 then any_18to20 = 2;
if any_18to21 = 0 then any_18to21 = 2;
if any_18to22 = 0 then any_18to22 = 2;
if any_18to23 = 0 then any_18to23 = 2;

rename
any_16to18 = AGE1618
any_18to19 = AGE1819
any_18to20 = AGE1820
any_18to21 = AGE1821
any_18to22 = AGE1822
any_18to23 = AGE1823

age19_status = STATUS19
age20_status = STATUS20
age21_status = STATUS21
age22_status = STATUS22
age23_status = STATUS23

age_18_yr = YRAGE18
pe1_s_age = AGE1PLC
;


/* Assign Gender Class */

select(GENDER_CD);
   when('F') GENDER=1;                                /* Female */
   when('M') GENDER=2;                                /* Male */
   when('I') GENDER=3;                                /* Intersex */
   otherwise GENDER=99;                               /* Unknown/Missing */
end;

/* Assign Ethnic Class */

select(P_ETHNCTYC) ;
   when(.,0,6351,6352,6453) ETHNIC=99 ;              /* Missing */
   when(823,826) ETHNIC=1 ;                          /* Black */
   when(839,840,841,842,843,844) ETHNIC=2 ;          /* White */
   when(830,3162,3163,3164,3165) ETHNIC=3 ;          /* Hispanic */
   when(820,821) ETHNIC=5 ;                          /* Native Am */
   when(5922,5923,822,824,825,827,828,829,
        831,832,833,834,835,836,837,838) ETHNIC=4 ;  /* Asian */
    otherwise ETHNIC=99 ;                            /* Invalid Value */
end ;
if HISP_CD eq 'Y' then ETHNIC=3 ;                    /* Hispanic */

/* Last Placement Type at Age 18 */

if last_age18_ohp in ('2222') then L_PLC18 = 1;                 /* Pre-Adopt */
else if last_age18_ohp in ('1421','1422') then L_PLC18 = 2;     /* Kin */
else if last_age18_ohp in ('1415','1416') then L_PLC18 = 3;     /* Foster */
else if last_age18_ohp in ('1414','2200') then L_PLC18 = 4;     /* FFA */
else if last_age18_ohp = '1419' then L_PLC18 = 9;               /* Court Specified Home */
else if last_age18_ohp in ('7208') then L_PLC18 = 9.2;          /* Tribe Specified/Approved Home */
else if last_age18_ohp = '1417' then L_PLC18 = 10;              /* Group/STRTP */
else if last_age18_ohp in ('1418','7027') then L_PLC18 = 11;    /* Shelter */
else if last_age18_ohp = '5411' then L_PLC18 = 14;              /* Guardian */
else if last_age18_ohp = '6716' then L_PLC18 = 19;              /* SILP */
else if last_age18_ohp not in (' ','0') then L_PLC18 = 29;      /* Other */
else if last_age18_ohp in (' ','0') then L_PLC18 = 99;          /* Missing */

if PREDOM_FT = 13 then PREDOM_FT = 29;

;run;

data test2a;
set test2;

AGENCY = 4;

data test3;
set test2 test2a;

rename
GENDER = GENDER_CD;

run;

data test3a;
set test3;

COUNTY = 0;

run;

data test4;
set test3a test3;

run;

data test4a;
set test4;

where 1901 <= COUNTY <= 1999;

COUNTY = 1900;

run;

data test5;
set test4 test4a;

run;

proc sql;
create table dvlp.edd as
select COUNTY length=3, AGENCY length =3, FLAG_8 length=3, GENDER_CD length=3, ETHNIC length=3, PREDOM_FT,
       TIME_IN length =3, YRAGE18 length=3, L_PLC18, AGE1PLC length=3, AGE1618 length = 3,
       AGE1819 length = 3, AGE1820 length = 3, AGE1821 length = 3, AGE1822 length = 3, AGE1823 length = 3, 
       STATUS19 length=3, STATUS20 length=3, STATUS21 length=3, STATUS22 length=3, STATUS23 length=3, 
       count(*) as COUNT length=4 format=comma12.
from test5
group by COUNTY, AGENCY, FLAG_8, GENDER_CD, ETHNIC, PREDOM_FT, TIME_IN, YRAGE18, L_PLC18, AGE1PLC, AGE1618, 
         AGE1819, AGE1820, AGE1821, AGE1822, AGE1823, STATUS19, STATUS20, STATUS21, STATUS22, STATUS23;
quit;

proc sort data = dvlp.edd;
by COUNTY AGENCY YRAGE18;
run;

data test.edd;
set dvlp.edd;
run;