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
earn_yr_17
earn_yr_18
earn_yr_19
earn_yr_20
earn_yr_21
earn_yr_22
earn_yr_23
CNTY_SPFCD
HISP_CD
COUNTY
age_18_yr;

if earn_yr_17 = . then earn_yr_17 = 0; 
if earn_yr_18 = . then earn_yr_18 = 0;
if earn_yr_19 = . then earn_yr_19 = 0;
if earn_yr_20 = . then earn_yr_20 = 0;
if earn_yr_21 = . then earn_yr_21 = 0;
if earn_yr_22 = . then earn_yr_22 = 0;
if earn_yr_23 = . then earn_yr_23 = 0;

run;

proc print data = test1;

where earn_yr_17 = . or
earn_yr_18 = . or
earn_yr_19 = . or
earn_yr_20 = . or
earn_yr_21 = . or
earn_yr_22 = . or
earn_yr_23 = .;
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
earn_yr_17 = EARNYR17
earn_yr_18 = EARNYR18
earn_yr_19 = EARNYR19
earn_yr_20 = EARNYR20
earn_yr_21 = EARNYR21
earn_yr_22 = EARNYR22
earn_yr_23 = EARNYR23

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

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

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.edd2 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,
       EARNYR16 length = 3, EARNYR17 length = 3, */ EARNYR18 length = 3, EARNYR19 length = 3, EARNYR20 length = 3,
       EARNYR21 length = 3, EARNYR22 length = 3,
       STATUS18 length=3, STATUS19 length=3, STATUS20 length=3, STATUS21 length=3, STATUS22 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, EARNYR16, EARNYR17, */ EARNYR18, EARNYR19, EARNYR20, EARNYR21,
         EARNYR22, STATUS18, STATUS19, STATUS20, STATUS21, STATUS22;
quit;

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

data test.edd2;
set dvlp.edd2;
run;