Use SAS Macro to build SQL params for Proc SQL

[Exie]

Hi,

I have some SAS code with a PROC SQL query in it, however I want to be able to pass in some variables and have a macro join them all together ready for the WHERE clause in my PROC SQL.

So far I have something like so:
%let claimant =%sysget(claimant);
%let policy_no =%sysget(policy_no);
%let soundex =1;
%let dob =%sysget(dob);
%let employer =%sysget(employer);

%let output_file =%sysget(output_file);

%macro query;
%global querystr;
%local comparison;

* Set comparison type ;
%if &soundex %then %let comparison=%str(=*);
%else %let comparison=%str(=);


* Insert policy number into final query ;
%if &policy_no %then %let querystr=;
%else %let querystr=%str(policy = &policy_no);


* Insert claimant into final query ;
%if &claimant %then ;
%else %let querystr=%str(&query_str AND claimany &comparison "&claimant"

* Insert dob into final query ;
%if &dob %then ;
%else %let querystr=%str(&query_str AND birth_dt = "&birth_dt"

* Insert employer into final query ;
%if &employer %then ;
%else %let querystr=%str(&query_str AND insured &comparison "&employer"
%mend;

%query;



The issues is of course :-
If one of the previous vars is not populated it will start with 'AND ...' the only way I can think of around this is a big ugly nested IF to check if any of the others had been used.

Has anyone else done this ?
Any examples of a better way to do this ?
Any suggestions on how I might go about it ?

Thanks,
Tim.

 

[Exie]

I've played around with the above code and come up this which seems to do what I want :

%let claimant =ABCD;
%let policy_no =;
%let soundex =1;
%let dob =01-JAN-1901;
%let employer =;

%macro query;
%global querystr;
%local comparison;

* Set comparison type ;
%if &soundex %then %let comparison=%str(=*);
%else %let comparison=%str(=);

%let querystr=WHERE 1=1 ;

* Insert policy number into final query ;
%if &policy_no eq %then ;
%else %let querystr=%str(&querystr AND policy = &policy_no);

* Insert claimant into final query ;
%if &claimant eq %then ;
%else %let querystr=%str(&querystr AND claimany &comparison "&claimant"

* Insert dob into final query ;
%if &dob eq %then ;
%else %let querystr=%str(&querystr AND birth_dt = to_date(%bquote('&dob'),ddmomyyyy));

* Insert employer into final query ;
%if &employer eq %then ;
%else %let querystr=%str(&querystr AND insured &comparison "&employer"
%mend;

%query;
%put querystr is: &querystr;

 

[teralearner]

All you need to check is if querystr is blank. You can start checking that when you check claimant. You also need this at the end to make sure these is at least one condition in querystr or .....

 

[ChrisW75]

An alternative is to do something like this (I saw a guy do it yesterday)

%let criteria1 = AND Claimant_id = 1234;
%let criteria2 = AND policy_no = 'A1049';

proc sql;
select *
from table1
where 1=1
&criteria1
&criteria2
;
quit;

This should work fine as 1 always eauals 1. Also, if you declare all of your macro variables beforehand, if some of them aren't set, it won't stick the macro variable name into your query. It'll save alot of nasty nested ifs.