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;
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
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.
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.
Thanks for the 'AND' Tip!!
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.
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;
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: