Biked to the train station this morning…felt the wind, watched trees waving in the breeze…flew past Highway 403 with trucks whizzing by…felt noble about being environmentally conscious…took the side path to the station…slowed to a halt beside the bike parking lot…opened my backpack to take out the key to lock my bike…Yikes! I couldn’t find it. Oh, no, forgot my key at home! 

What was I going to do?

I saw a fellow biker pulling up and asked him an irrelevant question, “If I left my bike without locking, is it safe?”, he said “not really.” Now I’m worried with only 5 minutes to get to the train. How was I going to get home and return with the bike key? Then another fellow biker came up to me and generously said “I’ll lock up my bike with yours, give you my combination code and when you return just lock up my bike please.” I was eternally grateful to this Good Samaritan. The train arrives. I hop on and take a drink out of my SAS bike water bottle, while mentally repeatedly thanking the helpful stranger for his kindness.

Now here’s a student problem that I just have to share with you that relates to this morning’s episode:

Given current month and custmths (number of months since customer started their account) in the June 2011 dateset, I want to get date_service_start (sas date value). With half a million records I really want an efficient coding solution that reduces all manual calculation.

 Here's an example of what I want:
current month = June 2011
custmths = 12 months
date_service_start = June 2010 since customer started 12 months before June 2011

The program uses a macro - the same macro can be reused in your proc and data steps. It uses a macro to use and reuse values much like the generous gesture of my fellow biker sharing his bike lock. Just like I didn’t have to return home and bike back to the station again, you can also save yourself enormous time--You don’t have to run the mdy function to loop through the data step half a million times. Here’s the solution:

Create variable current_month by using macro function syssfunc to get the sas date value for current date. Macros are great -you can use them over and over again. Macros are also more efficient since you resolve the value once and don’t have to run the mdy function half a million times for the “real data.” Thanks to Bruce Reed for this tip.

%let current_month = %sysfunc(mdy(6,1,2011));
data test;

To include current_month in the output data set and to have it formatted as monyy7.
create a data step variable from  the macro variable on the retain statement.

retain current_month &current_month;
input custmths; 

INTNX function uses an interval of 'month', uses current date value. The minus sign in front of the custmths variable cleverly counts backwards from current_month (Kind courtesy Linda Jolley), keyword 'b' tells SAS to tick over to the beginning of the month. 

date_service_start = intnx('month',&current_month,-custmths, 'b');
format current_month date_service_start monyy7.;
cards;
231
232
233
234
235
236
237
238
239
240
;
run;
proc print data=test;
run;

Here you have it, Using a combination of the INTNX function, the RETAIN statement, and a macro, customer got what she wanted—date_service_start neatly calculated. Hope you found this helpful. I’d love to hear your success stories with the INTNX function or anything else that you reuse in SAS.