Macro to create dummy variables

Solved
Frequent Contributor
Posts: 81

Macro to create dummy variables

Hi,

My dataset looks like

ID JoinDate Churn_Date Tenure

1 12Nov2011 31Dec2011 2

2 14Dec2011 30Apr2012 5

...

Churn Date is always month end, Tenure is number of months between joinDate and Churn_Date from the first day of month of JoinDate.

I'm trying to create dummy variables for reference of the MonthEnd date from JoinDate, my ideal dataset would look like:

ID JoinDate Churn_Date Tenure MonthEnd_1 MonthEnd_2 MonthEnd_

1 12Nov2011 31Dec2011 2 31Nov2011 31Dec2011

2 14Dec2011 30Apr2012 5 31Dec2011 31Jan2012 29Feb2012 ...

the X will be defined by the number of months Tenure and the function i'm using to get MonthEnd date is:

MonthEnd_1 = intnx('month',joindate,0,'e');

MonthEnd_2 = intnx('month',joindate,1,'e');

etc.

Because Tenure will be dynamic month to month, I was hoping to create a macro to automate this process of creating these dummy variables in my dataset, any help is greatly appreciated.

Thank you!

Accepted Solutions
Solution
‎05-06-2013 02:59 PM
Super User
Posts: 8,120

Re: Macro to create dummy variables

data want ;

set have ;

array MonthEnd_ (77) ;

format MonthEnd_: date9. ;

do _n_=1 to tenure ;

MonthEnd_(_n_) = intnx('month',joindate,_n_-1,'e');

end;

run;

Now if you want you can make the 77 above be dynamic by finding the maximum value of TENURE from the data.

proc sql noprint ;

select max(1, max(tenure) ) into :maxtenure

from have

;

quit;

...

array MonthEnd_ (&maxtenure) ;

All Replies
Super User
Posts: 13,583

Re: Macro to create dummy variables

Frequent Contributor
Posts: 81

Re: Macro to create dummy variables

The Tenure variable currently ranges from 1 to a max of 77 months.

Solution
‎05-06-2013 02:59 PM
Super User
Posts: 8,120

Re: Macro to create dummy variables

data want ;

set have ;

array MonthEnd_ (77) ;

format MonthEnd_: date9. ;

do _n_=1 to tenure ;

MonthEnd_(_n_) = intnx('month',joindate,_n_-1,'e');

end;

run;

Now if you want you can make the 77 above be dynamic by finding the maximum value of TENURE from the data.

proc sql noprint ;

select max(1, max(tenure) ) into :maxtenure

from have

;

quit;

...

array MonthEnd_ (&maxtenure) ;

Frequent Contributor
Posts: 81

Re: Macro to create dummy variables

Thank you very much.

Is the sql step equivalent to creating the maxtenure parameter?

%let maxtenure=77;

🔒 This topic is solved and locked.