BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GunnerEP
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
GunnerEP
Obsidian | Level 7

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

6 REPLIES 6
Reeza
Super User

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
Rhodochrosite | Level 12

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
Obsidian | Level 7

Thanks for the 'AND' Tip!!

Kurt_Bremser
Super User

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
Super User Tom
Super User

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
Obsidian | Level 7

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

 

 

 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 8846 views
  • 1 like
  • 5 in conversation