Proc SQL With %IF %THEN Macro

[GunnerEP]

Hi all,

I am trying to execute a proc sql based on a condition.

One where condition in proc sql should run only on certain condition. Need some help to get it resolved.

Any inputs or thoughts please ?

--- Current Code ---

proc sql noprint;
create table validate
as
select city,street,address,state_province_cd from master_table a master_table b
where a.city = b.city and a.state_province_cd = b.state_province_cd and a.address = b.address
group by city,street,address,state_province_cd;

run;

--- New Code Required ---

%macro validate;

%let cntry='IND';

proc sql noprint;
create table validate
as
select city,street,address,state_province_cd from master_table a master_table b
where a.city = b.city and
%if %eval(&cntry. in IND ARG) %then %str (AND a.state_province_cd = b.state_province_cd)
and a.address = b.address
group by city,street,address,state_province_cd;

run;

%mend validate;
%validate;

[Reeza]

Remember your macro needs to resolve to valid SAS code. 

There's no ELSE clause so if it doesnt execute what do you end up with, hint - check the number of ANDs. 

Your IF condition appears to be incorrect and should include values in brackets, %eval. 

[jklaverstijn]

Further ro what @Reeza already explained be careful withthe quotes around IND as the quotes become part of the value.

if you do

%let cntry='IND';

then the comparison

%if &cntry=IND

will always be false.

Also your code is in danger of generating two consecutive AND keywords if the %if resolves to false.

Always run with at least when debugging macro statements. It will help take out the guesswork.

options mprint mlogic symbolgen;

Regards,

- Jan.

[GunnerEP]

Thanks for the 'AND' Tip!!

[Kurt_Bremser]

For better readability, I keep macro code and SAS Base code in separate lines, like

%if condition %then %do;
base SAS code
%end;

This makes sure I put semicolons etc. where needed.

[Tom]

You need to end the %IF statment with a semi-colon.  You need to make sure your macro variable matches the values you are testing against.  If the macro variable has quotes then  the values you are comparing it to need to have quotes also.

%macro validate;

%let cntry=IND;

proc sql noprint;
  create table validate as
    select city,street,address,state_province_cd 
    from master_table a
       , master_table b
    where a.city = b.city 
      and a.address = b.address
%if %eval(&cntry. in IND ARG) %then 
      and a.state_province_cd = b.state_province_cd
; 
    group by city,street,address,state_province_cd
  ;
quit;
%mend validate;
%validate;

[GunnerEP · Accepted solution]

Thanks all for the inputs. Much appretiated!!

So finally found what i was trying to do here! http://support.sas.com/kb/35/591.html

Modified my code to something like this,

where a.city = b.city and
%if %eval(&cntry. in IND ARG) %then %str (a.state_province_cd = b.state_province_cd AND);
a.address = b.address