topic Re: Proc SQL With %IF %THEN Macro in SAS Procedures

Proc SQL With %IF %THEN Macro

GunnerEP — Sat, 18 Jun 2016 05:42:15 GMT

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;

Re: Proc SQL With %IF %THEN Macro

Reeza — Sat, 18 Jun 2016 09:48:57 GMT

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.

Re: Proc SQL With %IF %THEN Macro

jklaverstijn — Sat, 18 Jun 2016 10:36:20 GMT

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.

Re: Proc SQL With %IF %THEN Macro

Kurt_Bremser — Sun, 19 Jun 2016 07:14:18 GMT

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.

Re: Proc SQL With %IF %THEN Macro

Tom — Sun, 19 Jun 2016 14:27:24 GMT

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;

Re: Proc SQL With %IF %THEN Macro

GunnerEP — Sun, 19 Jun 2016 15:20:15 GMT

Thanks for the 'AND' Tip!!

Re: Proc SQL With %IF %THEN Macro

GunnerEP — Sun, 19 Jun 2016 15:30:51 GMT

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