Help using Base SAS procedures

Proc SQL With %IF %THEN Macro

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Proc SQL With %IF %THEN Macro

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;


Accepted Solutions
Solution
‎06-23-2016 05:18 AM
Contributor
Posts: 35

Re: Proc SQL With %IF %THEN Macro

[ Edited ]

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

 

 

 

View solution in original post


All Replies
Super User
Posts: 19,851

Re: Proc SQL With %IF %THEN Macro

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. 

 

Super Contributor
Posts: 441

Re: Proc SQL With %IF %THEN Macro

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.

Contributor
Posts: 35

Re: Proc SQL With %IF %THEN Macro

Posted in reply to jklaverstijn

Thanks for the 'AND' Tip!!

Super User
Posts: 7,832

Re: Proc SQL With %IF %THEN Macro

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,074

Re: Proc SQL With %IF %THEN Macro

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;
Solution
‎06-23-2016 05:18 AM
Contributor
Posts: 35

Re: Proc SQL With %IF %THEN Macro

[ Edited ]

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

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 2157 views
  • 1 like
  • 5 in conversation