topic Re: PROC SQL - IF ELSE in SAS Programming

PROC SQL - IF ELSE

SASAna — Thu, 09 Feb 2017 21:06:41 GMT

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;

Re: PROC SQL - IF ELSE

ballardw — Thu, 09 Feb 2017 21:32:47 GMT

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

Re: PROC SQL - IF ELSE

Kurt_Bremser — Thu, 09 Feb 2017 21:34:09 GMT

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.