New SAS User

Completely new to SAS or trying something new with SAS? Post here for help getting started.
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wheddingsjr
Pyrite | Level 9

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_dtDate editedsubscriberidNumeric
clm_line_numNumeric   
drg_cdCharacter   
disch_dtCharacter   
ICD_DIAG1_CD Character   
ICD_DIAG2_CD Character   
ICD_DIAG3_CD Character   
DF_edited_subscriber IDNumeric   
hcpcs_proc_cdCharacter   
revenue_cdNumeric   
first_srv_dtDate   
Place_Srv_DescCharacter   
Clm_Sprv_Full_NameCharacter   

 

1 ACCEPTED SOLUTION

Accepted Solutions
wheddingsjr
Pyrite | Level 9

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??

View solution in original post

4 REPLIES 4
ballardw
Super User

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.

wheddingsjr
Pyrite | Level 9

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?

 

wheddingsjr
Pyrite | Level 9

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??

wheddingsjr
Pyrite | Level 9

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.

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 4 replies
  • 16441 views
  • 0 likes
  • 2 in conversation