topic Re: ERROR: Character expression requires a character format. in New SAS User

ERROR: Character expression requires a character format.

wheddingsjr — Wed, 18 Nov 2020 22:47:36 GMT

Hi all

I am running a query and get the above mentioned error which I dont understand why. I am running this code:

PROC SQL;
CREATE TABLE BCBSMA_EOL_FY19 AS
SELECT
		  a.First_Srv_Dt AS Admission_Date LABEL= 'Admission Date' FORMAT= mmddyy10.
		, a.int_clm_num AS Claim_Number LABEL= 'Claim Number' LENGTH= 8
		, a.DRG_CD AS DRG_Code LABEL= 'DRG Code' FORMAT= $4. LENGTH= 4	
		, a.Disch_DT AS Discharge_Date LABEL= 'Discharge Date' FORMAT= mmddyy10.
		, a.ICD_DIAG1_CD AS Dx_Code_1 LABEL= 'Dx Code 1' FORMAT= $9. LENGTH= 9
		, a.ICD_DIAG2_CD AS Dx_Code_2 LABEL= 'Dx Code 2' FORMAT= $9. LENGTH= 9
		, a.ICD_DIAG3_CD AS Dx_Code_3 LABEL= 'Dx Code 3' FORMAT= $9. LENGTH= 9	
		, a.DF_edited_subscriber_id AS EditedSubscriberID LABEL= 'Edited Subscriber ID' LENGTH= 8
		, '' AS F14 LABEL= 'F14' 
		, '' AS F15 LABEL= 'F15' 
		, CASE WHEN a.hcpcs_proc_cd IS NOT NULL THEN a.hcpcs_proc_cd ELSE a.cpt_proc_cd END AS Hcpcs_Cpt LABEL= 'Hcpcs/Cpt' FORMAT= $10. LENGTH= 10
		, b.editedsubscriberid AS Member_ID LABEL= 'Member ID' LENGTH= 8
		, . AS Other
	    , a.revenue_cd AS Rev_Code LABEL= 'Rev Code' LENGTH= 8
		, a.First_Srv_Dt AS Service_Date LABEL= 'Service Date' FORMAT= mmddyy10.
		, a.Place_Srv_Desc AS Service_Location_Type LABEL= 'Service Location Type' FORMAT= $45. LENGTH= 45
		, a.Clm_Sprv_Full_Name AS Service_Provider_Name LABEL= 'Service Provider Name' FORMAT= $70. LENGTH= 70
		, 'BCBSMA_FY19_Received_Claims.xlsx' AS Table_Name LABEL= 'Table Name' FORMAT= $36. LENGTH= 36
FROM BCBSMA_MedicalClaims_FY19 a
	LEFT JOIN EOL_COHORT_FY19_1 b
		ON a.DF_edited_subscriber_id= b.EditedSubscriberID
;QUIT;

The join is from 2 data outputs from an earlier section of the code. I went through each one and they all seem to be ok, so I don't know why the error is there. Here is the results from the two datasets. Everything that is supposed to be character formatted is. Can anyone explain this? I would greatly appreciate any assistance.

BCBSMA_MedicalClaims_FY19 a		EOL_COHORT_FY19_1 b
first_srv_dt	Date		editedsubscriberid	Numeric
clm_line_num	Numeric
drg_cd	Character
disch_dt	Character
ICD_DIAG1_CD	Character
ICD_DIAG2_CD	Character
ICD_DIAG3_CD	Character
DF_edited_subscriber ID	Numeric
hcpcs_proc_cd	Character
revenue_cd	Numeric
first_srv_dt	Date
Place_Srv_Desc	Character
Clm_Sprv_Full_Name	Character

Re: ERROR: Character expression requires a character format.

ballardw — Wed, 18 Nov 2020 23:01:44 GMT

Run proc contents on your input data sets. I suspect one or more of your "date" variables are character.

Reason: SAS date formats are applied to numeric values and those are the only numeric formats I see in your code.

Date is not a SAS data type, character and numeric are it. If you are dealing with an external data system then you may need to do an explicit conversion.

Re: ERROR: Character expression requires a character format.

wheddingsjr — Wed, 18 Nov 2020 23:17:35 GMT

Thanks Ballard. You are right. The below is supposed to be a date but the variable is a character.

a.Disch_DT AS Discharge_Date LABEL= 'Discharge Date' FORMAT= mmddyy10.

How do I fix it?

Re: ERROR: Character expression requires a character format.

wheddingsjr — Wed, 18 Nov 2020 23:28:37 GMT

Never mind Ballard. I ran this and it worked:

data BCBSMA_MedicalClaims_FY19;
set  BCBSMA_MedicalClaims_FY19;
Disch_DT=input(Disch_DT_1,8.);
Drop Disch_DT;
rename Disch_DT_1=Disch_DT;
run;

Thanks for your help. How do I close this post??

Re: ERROR: Character expression requires a character format.

wheddingsjr — Wed, 18 Nov 2020 23:38:34 GMT

Looks like I spoke too soon. I no longer get the error message and the code runs, but there are no results in the column.