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 |
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??
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.
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?
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??
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.
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.