Using Macro logic when parameter is null

[David_S]

Hello all,

I'm trying to add macro logic to my definition to run even when the "enddt" positional parameter is null.  Please verify that my %if expression %then %do; logic is placed correctly within the macro definition and/or that the logic in itself is correct.  Essentially, my goal is to ignore the end date (enddt) an return employees hired on or after the start date (startdt).

%macro donations (startdt,enddt,outlib=work,status=married);

%let status=%sysfunc(propcase(&status));

proc sql;

     create table outlib..&status%substr(&startdt,6) as

     select c.employee_id "ID", employee_name "Name", salary format=dollar8.,

          qtr1, qtr2, qtr3, qtr4, sum(qtr1, qtr2, qtr3, qtr4) as tot_donation "Ann. Donation"

     from (select a.employee_id, employee_name, salary

               from sql.employee_payroll as a, sql.employee_addresses as b

               where a.employee_id = b.employee_id and

               employee_term_date is missing and

               ("&startdt"d <= employee_hire_date <= "&enddt"d) and

               Marital_Status = "%substr(&status,1,1)") as c

     left join

     sql.employee_donations as d

     on c.employee_id = d.employee_id;

     %if &enddt= %then %do;

          where &startdt >= employee_hire_date;

          title "Donations of &status Employees Hired &startdt or After";

     %end;

     %else %do;

          title "Donations of &status Employees Hired between &startdt and &enddt";

     %end;

quit;

%mend donations;

%donations(01Jan2006);

Thank you,

David

[Patrick · Accepted solution]

You need to generate the code/have the macro condition where the code needs to be in the SQL - so no, it's not placed correctly. Something like below (untested of course as there is no data) could work:

%macro donations (startdt,enddt,outlib=work,status=married);

  %let status=%sysfunc(propcase(&status));

  title "Donations of &status Employees Hired between &startdt and &enddt";

  %if %str(&enddt)=%str() %then

    %do;

      title "Donations of &status Employees Hired &startdt or After";

    %end;

  proc sql;

    create table outlib..&status%substr(&startdt,6) as

      select c.employee_id "ID", employee_name "Name", salary format=dollar8.,

        qtr1, qtr2, qtr3, qtr4, sum(qtr1, qtr2, qtr3, qtr4) as tot_donation "Ann. Donation"

      from

        (

          select a.employee_id, employee_name, salary

          from sql.employee_payroll as a, sql.employee_addresses as b

            where

              a.employee_id = b.employee_id

              and employee_term_date is missing

              and "&startdt"d <= employee_hire_date

              %if %str(&enddt) ne %str() %then

                %do;

                  and employee_hire_date <= "&enddt"d

                %end;

              and Marital_Status = "%substr(&status,1,1)"

          ) as c

        left join

          sql.employee_donations as d

          on c.employee_id = d.employee_id;

  quit;

  title;

%mend donations;

%donations(01Jan2006);

[David_S]

Thanks Patrick!

Yes, this did work.  I appreciate you reviewing this.

David