Birthday Promotion Solutions
Thank you to everyone who sent in their solutions to the Birthday Promotion quiz. There were several dozen entries, hence we are unable to publish everyone's entries. The selection below covers the range of techniques which were sent in.
The problem posed was as follows:
Consider yourself a SAS Programmer who works in a team that execute marketing campaigns with SAS. You wish to create a personalised message to your recipients if their birthday falls within active campaign dates.
Attached is a program that generates two SAS tables:
1. CAMPAIGNS (three fictitious campaigns)
2. POPULATION (50 fictitious customers and dates of birth).
Write a SAS program that:
a. Merges the two tables creating one row for each member of the population, for each campaign.
b. Creates a flag indicating whether the customers birthday (not date of birth!) falls between the campaign start and end dates.
The range of techniques sent it was quite diverse, however use of Proc SQL was noticable. Many people sent an example of both the Data Step and Proc SQL. This is an interesting insight to programming approaches and clearly endorses the emerging prevalence of SQL in SAS programming as predicted by Amaranayake and Shannon in 2008.
The following people sent working examples and several others who wish to remain anonymous. Well done.
Christopher Andrews
Albert Chau
Clare Horscroft
Dirk Van Krunckelsven
Adam Langron
Ankit Patel
Suhas K R
Paul Stephens
Steve Thompson
Keith Timms
Solutions
A selection of solutions are provided below. Please note the code layouts are modified from those provided to allow it to fit into the screen.
Approach 1
This anonymous solution uses a combination of Proc SQL and macro variables. This keeps the code quite readable and promotes parameterisation of the input variables.
%let startd = put(campaign_start,julday8.);
%let endd = put(campaign_end,julday8.);
%let birthd = put(date_of_birth,julday8.);
proc sql _method;
create table birthday as
select *,
((&birthd. GE &startd. AND &birthd. LE &endd.)
AND &startd. lt &endd.)
OR
((&birthd. GE &startd. OR &birthd. LE &endd.)
AND &startd. gt &endd.)
as flag label="Promotion"
from population, campaigns;
quit;
Approach 2
The following solution illustrates some powerful macro and data step programming techniques. Clare provided an elegant SQL solution, in addition to the generic option below. Clare has ensured this solution will process whatever the number of campaigns or populations. We liked this approach as it can easily be adopted to create unique output tables for each campaign.
%macro birthdays; *(Clare Horscroft);
%let dsid=%sysfunc(open(campaigns,in));
%let nocamps=%sysfunc(attrn(&dsid,nobs));
%if &dsid > 0 %then %let rc=%sysfunc(close(&dsid));
%let dsid=%sysfunc(open(population,in));
%let nopop=%sysfunc(attrn(&dsid,nobs));
%if &dsid > 0 %then %let rc=%sysfunc(close(&dsid));
data birthdays(drop=testyear);
set
%do i = 1 %to &nocamps.;
population
%end;
;
%do i = 1 %to &nocamps.;
if _n_ = %eval((&i.-1)*&nopop.+1) then
set campaigns(where=(cid=&i.));
%end;
testyear = year(campaign_start);
do until (testyear=year(campaign_end)+1
or birthdayflag = 1);
if mdy(month(date_of_birth),day(date_of_birth),testyear)
>= campaign_start and
mdy(month(date_of_birth),day(date_of_birth),testyear)
<= campaign_end then
birthdayflag = 1;
testyear = testyear + 1;
end;
run;
%mend;
%birthdays;
Approach 3
This SQL solution came from Keith Timms. A robust solution allowing for campaigns which run for over one calendar year, and a situation where dates of birth fall on 29th February.
proc sql;
create table bday_flag2 (drop=datecheck: year_dif) as
select *,
/* change birthday year to that of campaign_start */
case when month(date_of_birth)=2 and day(date_of_birth)=29
/* change 29th Feb birthdays to 28th */
then mdy(2,28,year(campaign_start)) between
campaign_start and campaign_end
else mdy(month(date_of_birth), day(date_of_birth),
year(campaign_start))
between campaign_start and campaign_end end
as datecheck1,
/* change birthday year to that of campaign_end */
case when month(date_of_birth)=2 and day(date_of_birth)=29
/* change 29th Feb birthdays to 28th */
then mdy(2,28,year(campaign_end))
between campaign_start and campaign_end
else mdy(month(date_of_birth), day(date_of_birth),
year(campaign_end))
between campaign_start and campaign_end end as
datecheck2,
/* calculates number of calendar years between dates */
yrdif(campaign_start,campaign_end,'act/act') as year_dif,
case when calculated year_dif<1 then
max(calculated datecheck1,calculated datecheck2)
else 1 /* 1+ calendar years between start and end dates */
end as birthday_flag
from campaigns, population;
quit;
Approach 4
Dirk Van Krunckelsven sent in the following SQL solution. This is quite similar to the solution put together by Amadeus.
proc sql noprint;
create table Bday_Campaign
as select *,
CASE
when year(campaign_start) eq year(campaign_end) then
(CASE
when campaign_start le MDY(month(date_of_birth),
day(date_of_birth),
year(campaign_start)) le
campaign_end then 1
else 0 end)
else
(CASE
when campaign_start le MDY(month(date_of_birth),
day(date_of_birth),
year(campaign_start))
le campaign_end OR
campaign_start le MDY(month(date_of_birth),
day(date_of_birth),
year(campaign_end ))
le campaign_end then 1
else 0 end)
end as Bday_flag
from campaigns, population;
quit;
Amadeus Software's Solution
Doubtless you will be wondering what option was originally used by Amadeus? We selected the SQL route, our solution is below.
proc sql;
create table solution as select *,
case
when((mdy(month(date_of_birth), day(date_of_birth),
year(campaign_start)) between
campaign_start and campaign_end)
or
(mdy(month(date_of_birth),
day(date_of_birth),year(campaign_end))
between campaign_start and campaign_end)) then 1
else 0
end as flag
from campaigns,population;
quit;
