- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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??
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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??
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.