07-07-2017 10:40 AM
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'.
88 CREATE TABLE obhdata.azc_suicidegrant_07062017 AS
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.
07-07-2017 10:53 AM - edited 07-07-2017 10:54 AM
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 ?!
07-07-2017 02:26 PM
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.
07-10-2017 10:09 AM
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!