proc sql outobs=500;
create table NPSAS20_Enrollment as
select distinct P.UID label='Student ID',
P.NAME_FIRST label='First Name',
P.NAME_MI label='Middle Name',
P.NAME_LAST label='Last Name',
P.SUFFIX label='Name suffix',
P.GENDER label='Sex',
case when E.Military_Status in ('A','N') then 2
when E.Military_Status in ('E','R','V','Y') then 1
when E.Military_Status = 'D' then 0
when E.Military_Status is null then 0
else -1 end label='Veteran or Active Duty Military status',
case when E.DUAL_ENROLL_IND='N' then 0
when E.DUAL_ENROLL_IND in ('O','P','S','V') then 1
else -1 end as Dual label='Dual enrollment indicator',
substr(P.BIRTH_YYYYMM_DT, 5, 2) as MonthofBirth label='Date of birth month',
substr(P.BIRTH_YYYYMM_DT, 7, 2) as DateofBirth label='Date of birth day',
substr(P.BIRTH_YYYYMM_DT, 1, 4) as YearofBirth label='Date of birth year',
case when P.HISPANIC_FLG = 'Y' then 1
when P.HISPANIC_FLG = 'N' then 0
else -1 end as Ethnicty label='Ethnicity',
case when P.WHITE_FLG = 'Y' then 1
when P.WHITE_FLG = 'N' then 0
else -1 end as White label='Race:White',
case when P.BLACK_AFRICAN_AM_FLG = 'Y' then 1
when P.BLACK_AFRICAN_AM_FLG = 'N' then 0
else -1 end as Black label='Race:Black or African American',
case when P.ASIAN_FLG = 'Y' then 1
when P.ASIAN_FLG = 'N' then 0
else -1 end as Asian label='Race:Asian',
case when P.AM_IND_ALASKAN_FLG = 'Y' then 1
when P.AM_IND_ALASKAN_FLG = 'N' then 0
else -1 end as AmericanIndian label='Race:American Indian or Alaska Native',
case when P.NAT_HAWAII_PAC_IS_FLG = 'Y' then 1
when P.NAT_HAWAII_PAC_IS_FLG = 'N' then 0
else -1 end as NativeHawaiian label='Race:Native Hawaiian or Pacific Islander',
case when A.SPRADDR_ATYP_CODE = 'PM' then A.SPRADDR_STREET_LINE1 else '' end as Addressline1 label='Permanent address 1'
from SID.SID_Person_Demo P
inner join SID.SID_Enrollments_All_Vw E on P.UID=E.UID
left join Prod.Spraddr A on E.OASIS_PIDM=A.SPRADDR_PIDM
Where E.TERMID IN ('201905','201908','202001')
and E.BENCH='B';
select * from NPSAS20_Enrollment;
QUIT; Here are my codes. Three tables are used. The program works fine until I joined Prod.Spraddr into the program.
... View more
