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
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);
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);
Thanks Patrick!
Yes, this did work. I appreciate you reviewing this.
David
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.