Case statement isn't working with this code

[wheddingsjr]

Hi all

I ran this code:

PROC SQL;
CREATE TABLE WORK.EOL_Cohort AS
SELECT DISTINCT '' AS Disease LABEL= 'Disease' FORMAT= $60. LENGTH= 60
	, b.Final_DC 
	, Patient_Birth_Date AS DOB LABEL= 'DOB' FORMAT= mmddyy10.
	, Patient_Death_Date AS DOD LABEL= 'DOD' FORMAT= mmddyy10.
	, a.CLM_MEM_NUM
	, a.EditedSubscriberID
	, FirstName
	, LastName
	, 'THP' AS Payer LABEL= 'Payer' format $5.
	, '' AS Primary_Physician LABEL= 'Primary Physician' FORMAT= $30. LENGTH= 30
	, '' AS Quarter LABEL= 'Quarter'
	, T.PatientRaceDSC
	, CASE WHEN T.PatientRaceDSC = 'ASIAN' THEN 'Asian'
		   WHEN T.PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
		   WHEN T.PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
		   WHEN T.PatientRaceDSC like ('WHITE%') THEN 'White or Caucasian'
		   WHEN T.PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
		   WHEN T.PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
	 	   WHEN T.PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
		   WHEN T.PatientRaceDSC IS NULL THEN 'Unknown'
		 ELSE 'Other' END AS RACE
	, CASE WHEN s.EthnicGroupDSC in ('Yes Hispanic','Hispanic') THEN 'YES' 
	          ELSE 'NO' END AS HISPANIC_OR_LATINO
	, R.PermanentStateDSC AS Residence LABEL= 'Residence'
	, R.GenderDSc AS Sex LABEL= 'Sex'
	, R.Age LABEL= 'Age'
	, R.DFCIMRN as MRN LABEL= 'MRN'
	, '' as BMT
	, '' as Edited_Subscriber_ID
FROM WORK.ID_MRN a
	left join PatExt.PatientExtension r
	   on a.MRN=input(r.DFCIMRN,15.)
    left join WORK.DiseaseCtr b
       on b.MRN=a.MRN
	left join pat.Patient s
		on s.MRN = r.DFCIMRN
	left join Pat.race t
		on s.PatientID = t.PatientID;
WHERE (T.LineNBR = 1 OR T.LineNBR IS NULL)
;quit;

and everything seemed to work, but upon closer examination it really didn't. The T.PatientRaceDsc column brings back the correct column (I only added it to make sure the case statement worked and got the same results), but as you can see from the attachment they are not the same. I tried taking the T. off in the case statement and that gave me the same results. Should there be parenthesis or something in the statement?

[Tom · Accepted solution]

Looks like it is doing what you wanted. Since the value did not match any of the WHEN clauses you got the value from the OTHER clause.

Your CASE statement is looking for values that start with WHITE and the values in the XLSX file you posted start with White. 

[wheddingsjr]

Thanks Tom, that was exactly the problem.