CODEWITHSUNDEEP
sasconcatenationwhere-clause
Wolfspirit
Wolfspirit

Reputation: 155

Concatenated where conditional in SAS

I am trying to write a macro to perform a proc summary operation on a dataset.

This macro takes in a few variables:

These variables are fed into the where statement in the macro below. Note that there will sometimes be an 'and' condition, and sometimes not at all.

%macro program1(start,end,cond); %macro _(); %mend _;
    proc summary data = data1 nway missing;
        where &start. < month < &end. || &cond.;
        class month_inc;
        var var1 var2;
        output out =summ_data1 sum=;
    run;
%mend;

I am having trouble getting the macro variables to work correctly in the where statement. The || doesn't work.

I tried to use CATX like so:

where catx(' ',&start. le month_inc le &end.,&cond.);

And that works to an extent. The &start. and &end. variables resolves as numerical values instead of the SAS dates, thus not summarizing my results correctly.

Upvotes: 0

Views: 430

Answers (1)

Tom
Tom

Reputation: 51566

If you literally pass in the keyword AND as part of the value of the parameter COND (cond=and state='XXX') then just remove the concatenation operator from your WHERE statement.

where (&start<month<&end) &cond ;

Otherwise use macro logic to conditionally generate the part that references the optional COND value.

I like to use the WHERE ALSO (also known as where and) statement to augment existing WHERE statements.

%macro program1(start,end,cond); 
  proc summary data = data1 nway missing;
    where &start. < month < &end. ;
%if %length(&cond) %then %do;
    where also &cond.;
%end;
    class month_inc;
    var var1 var2;
    output out =summ_data1 sum=;
  run;
%mend;

So then your example call could look like this:

%program1(start = '01Jan2013'd
         ,end = '01Jan2016'd
         ,cond = state = 'XXX')

Upvotes: 1

Related Questions