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
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.
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.