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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.