DATA Step, Macro, Functions and more

Using Macro logic when parameter is null

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Using Macro logic when parameter is null

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


Accepted Solutions
Solution
‎04-12-2015 12:12 AM
Respected Advisor
Posts: 3,892

Re: Using Macro logic when parameter is null

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);

View solution in original post


All Replies
Solution
‎04-12-2015 12:12 AM
Respected Advisor
Posts: 3,892

Re: Using Macro logic when parameter is null

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);

Occasional Contributor
Posts: 16

Re: Using Macro logic when parameter is null

Thanks Patrick!

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

David

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 190 views
  • 0 likes
  • 2 in conversation