DATA Step, Macro, Functions and more

PROC SQL - IF ELSE

Reply
Frequent Contributor
Posts: 95

PROC SQL - IF ELSE

 

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 Smiley TongueNQ_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;

 

Super User
Posts: 11,343

Re: PROC SQL - IF ELSE

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

Super User
Posts: 7,799

Re: PROC SQL - IF ELSE

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 2 replies
  • 926 views
  • 0 likes
  • 3 in conversation