BookmarkSubscribeRSS Feed
SASAna
Quartz | Level 8

 

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;

 

2 REPLIES 2
ballardw
Super User

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

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2691 views
  • 0 likes
  • 3 in conversation