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?
PROC SQL;
SELECT count(distinct B.NEW_PROV_BIL_TIN) INTO :PNQ_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
;
QUIT;
%put &=PNQ_obs;
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
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.