DATA Step, Macro, Functions and more

Describe and Incorrect Syntax Error HELP ME!!!

Reply
Occasional Contributor
Posts: 5

Describe and Incorrect Syntax Error HELP ME!!!

Hello everyone! I'm new to writing code and while i'm super excited, i'm definitely feeling the first-timer frustrations. I wrote the following code and received the following errors. I'm totally stuck! Any and all help is appreciated. THANKS A MILLION!

 

OPTIONS OBS= MAX;
* Pull claims - CY2016;
PROC SQL;
	%include "!sasroot\Include\Before_Mars4_MDW_dhh.txt";
	CREATE TABLE suicideclaim AS
		select * from connection to oledb

(select CLR_Recip_ID        as RecipID,	       CLC_Claim_ICN           as ICN, 	    
			CLR_Age_In_Years        as Age, 		   CLR_Recip_Parish        as Recip_Par,  
			LPAR_Parish_Desc        as Recip_Parish,   CLR_Race                as Race,         
			ELS_Family_Size         as Family_Size,	   ELS_Household_Size_Code as Household,  
			ELS_Gross_Income        as Gross_income,   ELS_Sex                 as Sex,      
			ELS_Marital_Status_Code as Marital_status, ELS_TANF_Cash_Code      as TANF, 
			ELS_SSI_Code            as SSI,            CLR_Aid_Category        as AC,
			CLC_Diag_Code_1         as Diag_1,	  	   CLC_Diag_Code_2         as Diag_2, 	       
            CLDX10_Diag_Code_1      as Dx10_1, 	       CLDX10_Diag_Code_2      as Dx10_2, 
            CLC_Service_from_date   as DOS,		       CLQ_Units_Of_Serv       as Unit,  
			CLP_Serv_Prov_Id        as ProvID, 	  	   CLP_Serv_Prov_Spec1     as PS,    	
			CLP_Serv_Prov_Type      as PT,     		   CLP_Serv_Prov_Parish    as Prov_Par,   
			LPPAR_Parish_Desc       as Prov_Parish,    CLR_Type_Case           as TC,
			Case
				where CLR_Age_In_Years ge 25
and where substr(LDC10_Diag_Code,1,3) in ('X40','X41','X42','X46','X47','Y10','Y11',
		'Y12','Y16','Y17','Y870','T39', 'T40', 'T423', 'T424', 'T427', 'T43', 'T509',
		'T58', 'X44')
		OR substr(LDC10_Diag_Code,1,3) BETWEEN 'X60' AND 'X84' 
		OR LDC10_Diag_Code = 'T1491'
			
		from CLQ 
			inner join CLC    on CLQ_Time_Key = CLC_Time_Key    and CLQ_Claim_Key = CLC_Claim_Key 
			inner join CLR    on CLQ_Time_Key = CLR_Time_Key    and CLQ_Claim_Key = CLR_Claim_Key  
			inner join CLP    on CLQ_Time_Key = CLP_Time_Key    and CLQ_Claim_Key = CLP_Claim_Key

			inner join CLDX10 on CLQ_Time_Key = CLDX10_Time_Key and CLQ_Claim_Key = CLDX10_Claim_Key

			left  join LPAR  on CLR_Recip_Parish     = LPAR_Parish_Code
			left  join LPPAR on CLP_Serv_Prov_Parish = LPPAR_Parish_Code
			left  join ELS   on CLR_Recip_Id = ELS_Elig_Id
		
			where CLQ_Time_Key >= 201601 
				and CLC_Service_From_Date between 20160101 and 20161231
				and CLQ_claim_status in ('1', '2')
				and CLQ_Transaction_Type in (1, 2)
				
		); 
%include "!sasroot\Include\after_sql.txt"; 
PROC SQL
CREATE TABLE obhdata.azc_suicidegrant_07072017 AS 
   SELECT *
      FROM suicideclaim
	where substr(LDC10_Diag_Code,1,3) in ('X40','X41','X42','X46','X47','Y10','Y11',
		'Y12','Y16','Y17','Y870','T39', 'T40', 'T423', 'T424', 'T427', 'T43', 'T509',
		'T58', 'X44')
		AND substr(LDC10_Diag_Code,1,3) BETWEEN 'X60' AND 'X84' 
		AND LDC10_Diag_Code = 'T1491'
group by LPAR_Parish_Desc;
quit; 

The errors are

 

 

ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : Deferred prepare could not be completed.: Statement(s) could not be

prepared.: Incorrect syntax near the keyword 'where'.

 

PROC SQL

88 CREATE TABLE obhdata.azc_suicidegrant_07062017 AS

______

22

76

ERROR 22-322: Syntax error, expecting one of the following: ;, BUFFERSIZE, CHECK, CODEGEN, CONSTDATETIME, DOUBLE, DQUOTE,

ERRORSTOP, EXEC, EXITCODE, FEEDBACK, FLOW, INOBS, IPASSTHRU, LOOPS, NOCHECK, NOCODEGEN, NOCONSTDATETIME, NODOUBLE,

NOERRORSTOP, NOEXEC, NOFEEDBACK, NOFLOW, NOIPASSTHRU, NONUMBER, NOPRINT, NOPROMPT, NOREMERGE, NOSORTMSG, NOSTIMER,

NOSTOPONTRUNC, NOTHREADS, NOWARNRECURS, NUMBER, OUTOBS, PRINT, PROMPT, REDUCEPUT, REDUCEPUTOBS, REDUCEPUTVALUES,

REMERGE, SORTMSG, SORTSEQ, STIMER, STOPONTRUNC, THREADS, UNDO_POLICY, WARNRECURS.

ERROR 76-322: Syntax error, statement will be ignored.

Trusted Advisor
Posts: 1,400

Re: Describe and Incorrect Syntax Error HELP ME!!!

[ Edited ]

I see two issues:

 

1) PROC SQL; - shopuld end with semicolon. It is missing on the second one.

 

2) Your first lines are: 

OPTIONS OBS= MAX;
* Pull claims - CY2016;
PROC SQL;
	%include "!sasroot\Include\Before_Mars4_MDW_dhh.txt";

shouldn't  the %include line precede proc sql; line ?!

Occasional Contributor
Posts: 5

Re: Describe and Incorrect Syntax Error HELP ME!!!

Thank you so much! The missing semi-colon on PROC SQL was an issue. I've fixed it. The code is working now. Thanks again!

Super User
Super User
Posts: 6,502

Re: Describe and Incorrect Syntax Error HELP ME!!!

It does not look like you provided the code that is generating the error message.

If you want to see the code in the include file that corresponds to the error message add the /SOURCE2 option to the %INCLUDE statement.

Occasional Contributor
Posts: 5

Re: Describe and Incorrect Syntax Error HELP ME!!!

Hi Tom! I was able to solve this issue. My coworker looked through the code and found where I was missing the correct syntax (when versus where).

 

Thanks for your help!

Ask a Question
Discussion stats
  • 4 replies
  • 124 views
  • 0 likes
  • 3 in conversation