BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
David_S
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

2 REPLIES 2
Patrick
Opal | Level 21

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
Fluorite | Level 6

Thanks Patrick!

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

David

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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