Macro to create dummy variables

[DangIT]

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!

[ballardw]

Depending on your data ranges a macro may not be needed. Please see this thread for similar https://communities.sas.com/thread/43560

[DangIT]

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

[Tom · Accepted solution]

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

[DangIT]

Thank you very much.

Is the sql step equivalent to creating the maxtenure parameter?

%let maxtenure=77;