Multiple errors

[wheddingsjr]

Good afternoon all

I have a SAS query that i have used multiple times and I have no issues. I used it again this time changing the company name from HPHC to BCBSMA. When i run it using BCBSMA, i get several errors. Here is the code and the error messages from the log is beneath. Can anyone help me with this issue?

PROC SQL;
CREATE TABLE BCBSMA_EOL_FY19 AS
SELECT
		a.First_Srv_Dt AS Admission_Date LABEL= 'Admission Date' FORMAT= mmddyy10.
		, a.DF_edited_subscriber_id AS Claim_Number LABEL= 'Claim Number' FORMAT= $20. LENGTH= 20
		, 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= 'EditedSubscriberID' FORMAT= $20. LENGTH= 20
		, '' 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' FORMAT= $20. LENGTH= 20
		, . AS Other
		, input(substr(a.revenue_cd,2,4), best4.) AS Rev_Code LABEL= 'Rev Code'
		, 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 b
		ON a.DF_edited_subscriber_id= b.EditedSubscriberID
;QUIT;

ERROR: The LENGTH= specifier for Claim_Number is out-of-range. It should be a value between 3 and 8, inclusive.
ERROR: Character expression requires a character format.
ERROR: The LENGTH= specifier for EditedSubscriberID is out-of-range. It should be a value between 3 and 8, inclusive.
ERROR: The LENGTH= specifier for Member_ID is out-of-range. It should be a value between 3 and 8, inclusive.
ERROR: Numeric expression requires a numeric format.
ERROR: Numeric expression requires a numeric format.
ERROR: Numeric expression requires a numeric format.
ERROR: Function SUBSTR requires a character expression as argument 1.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
131 ! QUIT;
NOTE: The SAS System stopped processing this step because of errors.

[ballardw]

---

@wheddingsjr wrote:

Good afternoon all

I have a SAS query that i have used multiple times and I have no issues. I used it again this time changing the company name from HPHC to BCBSMA. When i run it using BCBSMA, i get several errors. Here is the code and the error messages from the log is beneath. Can anyone help me with this issue?

PROC SQL;
CREATE TABLE BCBSMA_EOL_FY19 AS
SELECT
		a.First_Srv_Dt AS Admission_Date LABEL= 'Admission Date' FORMAT= mmddyy10.
		, a.DF_edited_subscriber_id AS Claim_Number LABEL= 'Claim Number' FORMAT= $20. LENGTH= 20
		, 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= 'EditedSubscriberID' FORMAT= $20. LENGTH= 20
		, '' 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' FORMAT= $20. LENGTH= 20
		, . AS Other
		, input(substr(a.revenue_cd,2,4), best4.) AS Rev_Code LABEL= 'Rev Code'
		, 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 b
		ON a.DF_edited_subscriber_id= b.EditedSubscriberID
;QUIT;

ERROR: The LENGTH= specifier for Claim_Number is out-of-range. It should be a value between 3 and 8, inclusive.
ERROR: Character expression requires a character format.
ERROR: The LENGTH= specifier for EditedSubscriberID is out-of-range. It should be a value between 3 and 8, inclusive.
ERROR: The LENGTH= specifier for Member_ID is out-of-range. It should be a value between 3 and 8, inclusive.
ERROR: Numeric expression requires a numeric format.
ERROR: Numeric expression requires a numeric format.
ERROR: Numeric expression requires a numeric format.
ERROR: Function SUBSTR requires a character expression as argument 1.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
131 ! QUIT;
NOTE: The SAS System stopped processing this step because of errors.

---

If this code ran before without a problem then I will bet that it was on a different data set or sets.

The error messages tell me they are caused by these 3 lines:

 a.DF_edited_subscriber_id AS Claim_Number LABEL= 'Claim Number' FORMAT= $20. LENGTH= 20
 a.DF_edited_subscriber_id AS EditedSubscriberID LABEL= 'EditedSubscriberID' FORMAT= $20. LENGTH= 20
 b.EditedSubscriberID AS Member_ID LABEL= 'Member ID' FORMAT= $20. LENGTH= 20

AND that df_edited_subscriber_id and EditedSubscriberId are numeric variables. As such the length is limited to 3 to 8 characters and cannot use character formats. When you use the "somevariable as anothervariable" construct then the new variable is going to be of the same type as the source.

Is this actually run on the same data as previously? If not then there is probably some difference in how the data sets were read. Typically when I see variables have a change of type as implied by the messages then I tend to think that data is being read by Proc Import or a wizard using such and the differences in the source files mean that new types were assigned when read.

[Kurt_Bremser]

@ballardw Note that the company name is also part of the source dataset name, so these are most likely different datasets, and also most likely the result of PROC IMPORT, with the usual consequences.

[ballardw]

---

@Kurt_Bremser wrote:

@ballardw Note that the company name is also part of the source dataset name, so these are most likely different datasets, and also most likely the result of PROC IMPORT, with the usual consequences.

---

I believe that I did mention that in the last paragraph of my post.

[Kurt_Bremser]

Missed your last line.

[ballardw]

---

@Kurt_Bremser wrote:

Missed your last line.

---

My mind may just work a tad different.

First the immediate cause, i.e. the data type.

The possible underlying issue, such as Proc Import.

I wonder if this may shake up something in the OP's organization when the find out what are supposed to be identically structured data sets aren't.

[Kurt_Bremser]

This is most probably the result of using PROC IMPORT. In this company, DF_edited_subscriber_id has only numeric content (or is empty), so it was imported as a number, but your SQL expects a character column.

You need to fix the process through which the data arrives in SAS.

[wheddingsjr]

Thanks Kurt, and you are correct. The source document is not the same as it is for the other company. I will look into how the data is imported.

Thanks again

[wheddingsjr]

Hi Kurt

Sorry for getting back to you so late but I had some personal issues I was dealing with. I went to the source document and changed the format to text for all the columns i got the error in. Some how or another, when they are imported in, the revert back to numeric. Is there no way to get around this?

[Kurt_Bremser]

You need to look at the import process as such.

In which file format is the data delivered, and what is the code used to bring it into SAS?

Please show us that code.

[wheddingsjr]

Kurt

I am using an excel sheet. And the code that brings it into SAS is:

PROC IMPORT DATAFILE= "Data.xlsx"
	OUT= work.Datafile DBMS= XLSX REPLACE;
	RUN;

[Kurt_Bremser]

There you have it. You use the worst file format in terms of data transfer, and then have to use guessing on the part of SAS, which leads to unreliable results.

Have the data sent in csv (or other structured text) files, and use a data step to read those. Using the same data step code will provide consistent results.

[wheddingsjr]

Thanks Kurt

I tried the CSV route and that made matters worse unfortunately. I was however able to make some progress. All but one error message has been corrected by doing it the way I did it. This one seems rather simple but no matter what i try I cannot get it to work. Here is the log with the single error code.

114        PROC SQL;
115        CREATE TABLE BCBSMA_EOL_FY19 AS
116        SELECT
117        		a.First_Srv_Dt AS Admission_Date LABEL= 'Admission Date' FORMAT= mmddyy10.
118        		, a.int_clm_num AS Claim_Number LABEL= 'Claim Number' LENGTH= 8
119        		, a.DRG_CD AS DRG_Code LABEL= 'DRG Code' FORMAT= $4. LENGTH= 4	
120        		, a.Disch_DT AS Discharge_Date LABEL= 'Discharge Date' FORMAT= mmddyy10.
121        		, a.ICD_DIAG1_CD AS Dx_Code_1 LABEL= 'Dx Code 1' FORMAT= $9. LENGTH= 9
122        		, a.ICD_DIAG2_CD AS Dx_Code_2 LABEL= 'Dx Code 2' FORMAT= $9. LENGTH= 9
123        		, a.ICD_DIAG3_CD AS Dx_Code_3 LABEL= 'Dx Code 3' FORMAT= $9. LENGTH= 9	
124        		, a.DF_edited_subscriber_id AS EditedSubscriberID LABEL= 'Edited Subscriber ID' LENGTH= 8
125        		, '' AS F14 LABEL= 'F14'
126        		, '' AS F15 LABEL= 'F15'
127        		, 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'
127      ! FORMAT= $10. LENGTH= 10
128        		, b.editedsubscriberid AS Member_ID LABEL= 'Member ID' LENGTH= 8
129        		, . AS Other
130        	    , revenue_Cd AS Rev_Code LABEL= 'Rev Code'
131        		, a.First_Srv_Dt AS Service_Date LABEL= 'Service Date' FORMAT= mmddyy10.
132        		, a.Place_Srv_Desc AS Service_Location_Type LABEL= 'Service Location Type' FORMAT= $45. LENGTH= 45
133        		, a.Clm_Sprv_Full_Name AS Service_Provider_Name LABEL= 'Service Provider Name' FORMAT= $70. LENGTH= 70
134        		, 'BCBSMA_FY19_Received_Claims.xlsx' AS Table_Name LABEL= 'Table Name' FORMAT= $36. LENGTH= 36
135        FROM BCBSMA_MedicalClaims_FY19 a
136        	LEFT JOIN EOL_COHORT_FY19 b
137        		ON a.DF_edited_subscriber_id = b.EditedSubscriberID
138        ;
ERROR: Character expression requires a character format.

The problem is if I format them as character then I get all the other error messages that I was previously getting.

[Kurt_Bremser · Accepted solution]

The only numeric formats in your query that I see are the date formats, so at least one of your dates must have been read erroneously as character.