topic Re: Using Macro logic when parameter is null in SAS Programming

Using Macro logic when parameter is null

David_S — Sat, 11 Apr 2015 21:30:32 GMT

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

Re: Using Macro logic when parameter is null

Patrick — Sun, 12 Apr 2015 04:12:27 GMT

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

Re: Using Macro logic when parameter is null

David_S — Sun, 12 Apr 2015 12:51:35 GMT

Thanks Patrick!

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

David