Error 78-322

[wheddingsjr]

Hello all. I am getting the above mentioned Error and I am not sure why. I have read quite a few answers to others with this error but they weren't comparable to mine. The query is:

PROC SQL;
CREATE TABLE work.patient_cohort_w_Race AS
SELECT DISTINCT pc.*

	,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
		  WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
		  WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
		  WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
		  WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
		  WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
	 	  WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
		  WHEN PatientRaceDSC IS NULL THEN 'Unknown'
		  ELSE 'Other' END AS RACE
	, CASE WHEN EthnicGroupDSC in ('Yes Hispanic','Hispanic') THEN 'YES' 
	      ELSE 'NO' END AS HISPANIC_LATINO

FROM Fin.HospitalAccount HA 	
LEFT JOIN work.patient_cohort pc on HA.PatientID = pc.PatientID
LEFT JOIN pat.Race R on HA.PatientID = R.PatientID
LEFT JOIN pat.Patient P on HA.PatientID = P.PatientID

and this is the log with the error:

274  PROC SQL;
     ----
     78
ERROR 78-322: Expecting a ','.

275  CREATE TABLE work.patient_cohort_w_Race AS
276  SELECT DISTINCT pc.*
277
278      ,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
279            WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
280            WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
281            WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
282            WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or
282! Alaska Native'
283            WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native
283! Hawaiian or Other Pacific Islander'
284            WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
285            WHEN PatientRaceDSC IS NULL THEN 'Unknown'
286            ELSE 'Other' END AS RACE
287      , CASE WHEN EthnicGroupDSC in ('Yes Hispanic','Hispanic') THEN 'YES'
288            ELSE 'NO' END AS HISPANIC_LATINO
289
290  FROM Fin.HospitalAccount HA
291  LEFT JOIN work.patient_cohort pc on HA.PatientID = pc.PatientID
292  LEFT JOIN pat.Race R on HA.PatientID = R.PatientID
293  LEFT JOIN pat.Patient P on HA.PatientID = P.PatientID

[PaigeMiller]

The error in your code is before PROC SQL;

[wheddingsjr]

Thanks @PaigeMiller 

I sent the message to disregard my question because I figured it out and didn't know that you had already replied. The issue was that I didn't include 'quit;' to the end of the query. But now that creates another issue. I brought the code in from SQL and it runs. The PatientRaceDSC and the EthnicGroupDSC columns are created, however, they are not populating correctly. The rows in the PatientRaceDSC are all populated with 'UNKNOWN' and all the rows in the EthnicGroupDSC are all populated with 'NO' but that cant be so. I researched the CASE expression and it seems to be written correctly (I Think). I believe I have all the joins correct as well. Do you have any thoughts on what could be wrong?

[Tom]

You seem to be having a hard time describing your issue.

Do you mean that in one or more of your generated datasets that some of the variables are getting set to UNKNOWN for every observation?

If so you need to look at the values that the variables you used in the calculations had.  

You could start with doing a PROC FREQ on the input datasets for the variables used in the calculation.

proc freq data=pat.Race;
  tables PatientRaceDSC  / list missing;
run;

But you might not see the problem there as it might be the issue is in the joining.  You can check that better by including the input variables into the output dataset. 

CREATE TABLE work.patient_cohort_w_Race AS
SELECT DISTINCT pc.*,r.PatientRaceDSC  
....

Then you can check the values actually used in the calculations.

[Reeza]

Error is before this code.
If you cannot find it, restart your system to clear any left over bugs and run it all again and see if it errors out. If it does, post the code immediately before this as well.

[wheddingsjr]

Thanks @Reeza

I discovered the issue, but that just created a new one. Please see the reply to @PaigeMiller. Any insight on this would be greatly appreciated.

Thanks

[Reeza]

Please show the output from a PROC FREQ on the PatientRaceDSC column. You didn't prefix it so not sure what table it's from.

Following may also be useful:

proc freq data=patient_cohort_w_Race;
table PatientRaceDSC*Race / list missing;
run;

[wheddingsjr]

@Reeza

This is the results from the proc freq data=patient_cohort_w_Race; you suggested

671  proc freq data=patient_cohort_w_Race;
672  table PatientRaceDSC*Race / list missing;
ERROR: Variable PATIENTRACEDSC not found.
673  run;

[PaigeMiller]

This means you do not have a variable name PATIENTRACEDSC in data set PATIENT_COHORT_W_RACE. If you disagree with this error message, then show us PROC CONTENTS for data set PATIENT_COHORT_W_RACE.

[wheddingsjr]

Hi @PaigeMiller 

I am creating the PATIENT_COHORT_W_RACE table so it doesn't exist yet. I realized that I didnt have the aliases for the tables they variable does exist in and added them. I get the same results. Here is the code that creates the work.patient_cohort table which works fine:

PROC SQL;
CREATE TABLE work.patient_cohort AS
SELECT DISTINCT a.mrn
	, a.PatientNM
	, a.dob
	, a.payorNM
	, scan(a.patientnm,1,',') as lastname
	, scan(a.patientnm, 2, ',') as firstname
	, a.SubscriberID as InsuredsIDNumber
	, a.firstDt as cohort_entering_Date
	, a.planID
	, a.PayorID
	,COMPRESS(SubscriberID, '', 'kd') AS EditedInsuredsIDNumber

FROM work.dataPull_2 a 
	INNER JOIN work.cntByMRN b
		ON a.mrn= b.mrn
	WHERE 
/*	 payornm like '%HARVARD PILGRIM%' /*' reporting period*/
	 payorID = 110001
/*and *PayorID IN (170001);*TUFTS HEALTH PLAN*/

;QUIT;

Now I want to add the Race and the Ethnicity variable by creating the work.patient_cohort_w_Race table:

PROC SQL;
CREATE TABLE work.patient_cohort_w_Race AS
SELECT DISTINCT pc.*

	,CASE WHEN r.PatientRaceDSC  = 'ASIAN' THEN 'Asian'
		  WHEN r.PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
		  WHEN r.PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
		  WHEN r.PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
		  WHEN r.PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
		  WHEN r.PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
	 	  WHEN r.PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
		  WHEN r.PatientRaceDSC IS NULL THEN 'Unknown'
		  ELSE 'Other' END AS RACE
	, CASE WHEN p.EthnicGroupDSC in ('Yes Hispanic','Hispanic') THEN 'YES' 
	      ELSE 'NO' END AS HISPANIC_LATINO

FROM work.patient_cohort pc	
LEFT JOIN pat.Identity I on pc.MRN = I.PatientIdentityID
LEFT JOIN pat.Race R on I.PatientIdentityID = R.PatientID
LEFT JOIN pat.Patient P on I.PatientIdentityID = P.PatientID
;quit;

[PaigeMiller]

I specifically asked to see the PROC CONTENTS output for data set PATIENT_COHOR_W_RACE. Please show us that.

Also, when debugging code like this, show us the LOG, not the code. Thanks.

[Reeza]

PROC SQL;
CREATE TABLE work.patient_cohort_w_Race AS
SELECT DISTINCT pc.*,
r.PatientRaceDSC,

	,CASE WHEN r.PatientRaceDSC  = 'ASIAN' THEN 'Asian'
		  WHEN r.PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
		  WHEN r.PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
		  WHEN r.PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
		  WHEN r.PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
		  WHEN r.PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
	 	  WHEN r.PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
		  WHEN r.PatientRaceDSC IS NULL THEN 'Unknown'
		  ELSE 'Other' END AS RACE
	, CASE WHEN p.EthnicGroupDSC in ('Yes Hispanic','Hispanic') THEN 'YES' 
	      ELSE 'NO' END AS HISPANIC_LATINO

FROM work.patient_cohort pc	
LEFT JOIN pat.Identity I on pc.MRN = I.PatientIdentityID
LEFT JOIN pat.Race R on I.PatientIdentityID = R.PatientID
LEFT JOIN pat.Patient P on I.PatientIdentityID = P.PatientID
;quit;

proc freq data=patient_cohort_w_Race ;
table PatientRaceDSC*Race / list missing;
run;

Post the results from the result tab and the log.

[wheddingsjr]

{lease disregard. I found my issue

[wheddingsjr · Accepted solution]

Thanks @Reeza @PaigeMiller and @Tom 

I was able to figure it out and everything ran fine. Thanks for your heads ups and suggestions.