02-09-2017 04:06 PM
Hi SAS USers,
Need some help on SQL - IF
My code is giving the sytax error. DB_A_Table is a oracle table PL_TAB is excel datset.
Erroring at %IF TRIM(B.NEW_PLAT) = 'ABC' OR TRIM(B.NEW_PLAT) ='XYZ' - says syntax is wrong.
CAn this be written in different way if this coding method is wrong?
SELECT count(distinct B.NEW_PROV_BIL_TIN) INTO NQ_obs
FROM DB.A_Table A ,PL_TAB B
WHERE B.CLAIM_ID_NEW = '' AND (%IF TRIM(B.NEW_PLAT) = 'ABC' OR TRIM(B.NEW_PLAT) ='XYZ') %THEN %DO;
( TRIM(A.&PROV_BIL_TIN) || A.SITE = TRIM(B.NEW_PROV_BIL_TIN) ) %END;
%ELSE %DO; (A.&PROV_BIL_TIN = TRIM(B.NEW_PROV_BIL_TIN) %END;
AND A.DATE = &ARUN_DT
AND A.ID = &ID
02-09-2017 04:32 PM
Macro language does not have access to dataset variable values :%IF TRIM(B.NEW_PLAT) would require the MACRO processor to know the value of the variable New_plat in the referenced source B.
SQL does not use IF/then syntax at all.
Can you describe what you are attempting with this code? Provide some example data and the desired output with some rules.
Part of what you may be wanting is
Where B.CLAIM_ID_NEW = '' AND TRIM(B.NEW_PLAT) in ('ABC' ,'XYZ') Which on a where clause restricts the output to cases where this is true and the IN operator returns a true/false response based on the valur of the variable on the left of IN.
TRIM(A.&PROV_BIL_TIN) || A.SITE introduces a macro variable and the || concatenation operator is a likely cause of embedded spaces
02-09-2017 04:34 PM
Will never work. Macro statements are executed before the SQL code is even fed to the interpreter.
For conditions in SQL, use case/when/else/end to create variables, and use those results in a having clause.
Read the documentation for proc sql.
And use the "little running man" button to post code. Keeps formatting and prevents unwanted smileys.